database design

Use a stack of intermediate languages（e.g. ER-model) to come from the description of an application to a database schema.e.g. ER-model,From intuitive and semi-formal to technical and formal


miniworld







mapping to semantic datamodel
e.g.ER-model=>ER-diagram





mapping to datamodel
relational database model
=>relational database schema




Main Phases of Database Design

Requirements collection and analysis:
understand and document their data requirements
Result: set of users’ requirements


Conceptual design:Includes detailed descriptions of the entity types, relationships, and constraints
do not include implementation details,
concentrates on specifying the properties of the data, without being concerned with storage details

Logical design or data model mapping：
Implementation data model: such as the relational or the object oriented database model
Result: a conceptual database schema in the implementation data model of the DBMS (additionally: external schemas)

physical design:Internal storage structures, access paths, and file organizations for the database files are specified: internal schema
A database is An integrated, self-describing collection of related data
Related: Data has some relationship to other data. In a university we have students who take courses taught by professors.

Database Management System (DBMS)
An Example Database (Relation)
A Database contains
• User Data
• Metadata
• Indexes
• Application metadata







User Data：
data will be generally stored in tables with some relationships between tables.
Each table has one or more columns. A set of columns forms a database record.
Meta Data: Data about data
Data that describe how user data are stored in terms of table name, column name, data type, length, primary keys, etc.
Indexs：indexes provide an alternate means of accessing user data. Sorting and Searching.
allow the database to access a record without having to search through the entire table.