The document discusses the database development process and the three-level architecture of database design. It describes the five steps of the database development process as planning, data collection and analysis, database design, implementation, and data conversion/loading. It then explains the three levels of database design as the external level representing user views, the conceptual level describing logical data relationships, and the internal level detailing physical storage structures. The goal of the three-level design is to separate the user and physical views of the database.
The document discusses the database development process and the three-level architecture of database design. It describes the five steps of the database development process as planning, data collection and analysis, database design, implementation, and data conversion/loading. It then explains the three levels of database design as the external level representing user views, the conceptual level describing logical data relationships, and the internal level detailing physical storage structures. The goal of the three-level design is to separate the user and physical views of the database.
The document discusses the database development process and the three-level architecture of database design. It describes the five steps of the database development process as planning, data collection and analysis, database design, implementation, and data conversion/loading. It then explains the three levels of database design as the external level representing user views, the conceptual level describing logical data relationships, and the internal level detailing physical storage structures. The goal of the three-level design is to separate the user and physical views of the database.
The document discusses the database development process and the three-level architecture of database design. It describes the five steps of the database development process as planning, data collection and analysis, database design, implementation, and data conversion/loading. It then explains the three levels of database design as the external level representing user views, the conceptual level describing logical data relationships, and the internal level detailing physical storage structures. The goal of the three-level design is to separate the user and physical views of the database.
Objective : At the end of this unit, you should be able to: Learn activities to be performed in the database development process Name the Three-Level Architecture of database with phases of database design.
IT1768 Database Systems
Topic 6
Database Development Process
IT1768 Database Systems
Topic 6
The Database Development Process
The database development process includes
the following steps: 1) 2) 3) 4) 5)
Database planning Data requirement collection and analysis Database design (modelling) Database implementation Data conversion and loading
IT1768 Database Systems
Topic 6
The Database Development Process
1. Database Planning It is the management activities that allow the stages of the database application to be realized as efficiently and effectively as possible. The activities include: a) Define mission statement b) Define mission objectives c) Specifying the scope and boundaries of the database application and the major user views.
IT1768 Database Systems
Topic 6
The Database Development Process
2. Data requirement Collection and Analysis
Information is gathered for each user view as user requirements, including: a description of data used or generated details of how data is to be used/generated any additional requirements for the new/future application
Information gathered is then analyzed to
identify the user requirements and then documented in requirement specifications. IT1768 Database Systems
Topic 6
Database Development Process
3. Database Design Database design is the process of producing a detailed data model of a database. This can be activated by first specifying the schema of a database.
What is a Database Schema?
- A database schema is a description of the structure of the database. It includes the: o description of relations (tables) o relationships between relations o domains o business rules. Number of 3 character length
Example: 1 Department ( DepNo, Dname, Dlocation, Strength ) Text of 12 character length N IT1768 Database Systems
Business Rules Business Rules Restrictions on the businesss activities that must be reflected in the database and database applications
Examples : o Each account is designated with one account number. o One customer can have more than one account numbers o Account Balance cannot be negative o A minimum balance of $10 must be maintained at all time o A student must attend a course o A course can have zero or many students
IT1768 Database Systems
Topic 6
Database Development Process
3. Database Design The initial design of a database is a complex process involving many different approaches. It can deal with the following topics: Data modelling
Database Normalization A relational database can be modelled by Entity Relationship (ER) Modelling.
IT1768 Database Systems
Topic 6
Approaches in Database Design
Two approaches of database design: 1) Top-Down approach: Using Entity-Relationship model (ERM). More appropriate for complex database. It based on the user requirements and build data model. 2) Bottom-Up approach: Using Normalization. More appropriate for simple databases with a relatively small number of attributes.
IT1768 Database Systems
10
Topic 6
Database Development Process
4. Database Implementation Based on the data model, physically realise the database and application design. - Use Data Definition Language (DDL) to create database schemas and empty database files. - Use DDL to create any specified user views. - Create application programs, including database transactions. - Test the system with real data, if necessary, make enhancement. IT1768 Database Systems
11
Topic 6
Database Development Process
5. Data Conversion and Loading Transferring any existing data into the new database and converting any existing applications to run on the new database. Only required when new database system is replacing an old system. DBMS normally has utility that loads existing files into new database. Legacy database (Conversion, Migration) Modernizing database IT1768 Database Systems
12
Topic 6
The 3-Level Architecture of
Database
IT1768 Database Systems
13
Topic 6
Three-Level Architecture of DBMS1
Users view
External Level
Conceptual Level
Internal Level
Physical data organization IT1768 Database Systems
Reception
Purchasing
View 1
The 3-Level Architecture of Database
View 2
etc.
Design
Conceptual Schema
Internal Schema
View n
External to conceptual mapping
Conceptual to internal mapping
Database 14
Topic 6
The 3-Level Architecture of Database
External Level
The 3-Level Conceptual
Architecture Level of Database Internal Level
The 3 Level Architecture has the aim of enabling users to access the same data but with a personalized view of it. The distancing of the internal level from the external level means that users do not need to know how the data is physically stored in the database. This level separation also allows the Database Administrator (DBA) to change the database storage structures without affecting the users' views IT1768 Database Systems
15
Topic 6
The Conceptual Database Design
External Level - The external level reflects the users view of the database. It describes the part of the database that is relevant to each user. - This level ensures that users have NO access to any other data in the Database.
Conceptual Level - This level describes what data is stored in the database and the relationships among the data; these include the entities, data types, relationships, user operations and constraints in a database.
Hence, Logical Database Design (The What in the design)
- The process of constructing a model of the information used in an enterprise based on a specific data model. - Its independent of a particular DBMS and other physical considerations. However, it is dependent on the target data model. IT1768 Database Systems
16
Topic 6
The Physical Database Design
Internal Level - This is the physical representation (implementation) of the database on the computer. It describes the physical storage structure of the database. It uses physical data model to describe the complete details of data storage, access paths for the database, and how the datas are retrieved or inserted in the database. Data Definition Language (SQL) is used.
Hence, Physical Database Design (The How in the design)
- The process of producing a description of the implementation of the database on secondary storage; it describes the base correlations, file organizations and indexes used to achieve efficient access to the data, and any associated integrity constraints and security measures. - It requires the physical database designer to know the functionality of the target DBMS. IT1768 Database Systems
17
Topic 6
Three-Level Architecture of Database
External level
View 1
View 2
Sno FName LName Age
Conceptual level
Salary
Staff_No LName
Staff_No FName LName DOB Salary
Internal level
Note :At each level, we have schema to
describe the data in that level. The 3 schemas are only descriptions of data; the only data that actually exists is at the physical level. IT1768 Database Systems
Bno
Branch_No
Create Table STAFF {
int Staff_No; int Branch_No; char FName[15]; char LName[15]; date Date_of_Birth; float Salary; }; index Staff_No; index Branch_No; Sample Code
18
Topic 6
Summary The activities to be performed in the database development process The three-Level architecture of database with phases of database design
