Texas A&M University
Database Design
Overall Database Design Procedure
• Information-level design:
o information gathering
o rough E-R diagram using “user views” as a start
• Physical-level design: information-level design adapted for the specific DBMS that will be used
o Must consider characteristics, setup and syntax of the particular DBMS
Building DBMS from established applications
• Surveys and Existing Document can help detail information about database design
• Must contain particular elements
o Entity information
o Attribute (column) information
o Relationships
o Functional dependencies
o Processing information
• Identify and list all columns and give them appropriate names
• Identify functional dependencies
• Determine the tables and assign columns
Our class example: Team Bowling Tournament
The school has a bowling league on campus. A team of 3 members bowl as well as they can in 3 games. The highest scoring team wins scholarship money.
The sheet to keeps each teams scores is as follows:
|Team Score sheets |
|[pic] |
Bowl 4 games, (first one practice), write down your scores. No cheating!
|Name |Game 1 |Game 2 |Game 3 |
| | | | |
In a real tournament, what options would each have??
|Admin |Bowler |
|Determine high score |Enter contest |
|Determine low score | |
| | |
| | |
| | |
When Developing Tables, think about relationships
• One-to-One Relationship Considerations
o Simply including the primary key of each table as a foreign key in the other (“many”) table there is No guarantee that the information will match
o Solutions
▪ create a single table which is workable, but not the best solution
▪ create separate tables for customers and sales reps
• Include the primary key of one of them as a foreign key in the other
|One-to-One Relationship Considerations |
|[pic] |
• Many-to-Many Relationship Considerations
o create a new table whose primary key is the combination of the primary keys of the original tables
o Complex issues arise when more than two entities are related in a many-to-many relationship
o Many-to-many-to-many relationship: involves multiple entities
o Deciding between a single many-to-many-to-many relationship and two (or three) many-to-many relationships depends on independence between tables
|Many-to-Many Relationship Considerations |
|[pic] |
Cumulative User Design (Views)
• User view: set of requirements necessary to support operations of a particular database user
• Cumulative design: supports all user views encountered during design process
What users will need to view the bowling league data?
Information level Design
• For each user view:
1. Represent the user view as a collection of tables
|Represent the User View As a Collection of Tables |
|Step 1: Determine the entities involved and create a separate table for each type of entity |
|Step 2: Determine the primary key for each table |
|Step 3: Determine the properties for each entity |
|Step 4: Determine relationships between the entities |
|One-to-many, Many-to-many, One-to-one |
2. Normalize these tables
• Target is third normal form
3. Identify all keys in these tables
▪ Primary key
• Natural key: consists of a column that uniquely identifies an entity
o Also called a logical key or an intelligent key
• Artificial key: column created for an entity to serve solely as the primary key and that is visible to users
• Surrogate key: system-generated; usually hidden from users
o Also called a synthetic key
▪ Foreign keys
• column(s) in one table that is required to match value of the primary key for some row in another table or is required to be null
• Used to create relationships between tables
• Used to enforce certain types of integrity constraints
▪ Alternate keys
• column(s) that could have been chosen as a primary key but was not
▪ Secondary keys
• columns of interest strictly for retrieval
4. Merge the result of Steps 1 through 3 into the cumulative design
• Combine tables that have the same primary key to form a new table
• New table:
o Primary key is same as the primary key in the tables combined
o Contains all the columns from the tables combined
o If duplicate columns, remove all but one copy of the column
• Make sure new design is in third normal form
|Merge the Result into the Design |
|[pic] |
Table names: users, scores, team_awards, individual_awards, team, team_member
1. Create DB design (tables) for all bowlers using the information level steps (1-3)
2. Create DB design (tables) for all admins using the information level steps (1-3)
3. Merge the two together
4. Identify the one-to-one tables
5. Identify the many-to-many tables
DON’T WORRY ABOUT THE MATH, THE DB WILL DO THAT
|[pic] |
E-R Diagrams and Database Design Language (DBDL)
Basic E-R Diagram
• Visually represents database structure
• Rectangle represents each entity
o Entity’s name appears above the rectangle
• Primary key for each entity appears above the line in the entity’s rectangle
• Other columns of entity appear below the line in rectangle
• For each foreign key, a line leads from the rectangle for the table being identified to the rectangle for the table containing the foreign key
|E-R Diagram Example |
|[pic] |
|Symbols in E-R Diagrams |
|212 |
|190 |
|187 |
Create a basic E-R diagram for our league
Database Design Language (DBDL)
• Table name followed by columns in parentheses
o Primary key column(s) underlined
• AK identifies alternate keys
• SK identifies secondary keys
• FK identifies foreign keys
o Foreign keys followed by an arrow pointing to the table identified by the foreign key
|Database Design Language Example |
|[pic] |
DBDL E-R Diagram
• Letters AK, SK, and FK appear in parentheses following the alternate key, secondary key, and foreign key, respectively
|Entity-Relationship (E-R) Diagrams Example |
|[pic] |
Physical Level-Design
• Undertaken after information-level design completion
• Most DBMSs support primary, candidate, secondary, and foreign keys
• To enforce restrictions, DB programmers must include logic in their programs
Null and Entry subtypes
• Null
o Special value
o Represents absence of a value in a field
o Used when a value is unknown or inapplicable
• Splitting tables to avoid use of null values
• Entity subtype: table that is a subtype of another table
|Nulls and Entity Subtypes Examples |
|[pic] |
Top Down Design vs. Bottom Up
• Bottom-up design method
o Design starts at low level
o Specific user requirements drive design process
• Top-down design method
o Begins with general database that models overall enterprise
o Refines model until design supports all necessary applications
E-R Model
• An approach to representing data in a database
• Entities are drawn as rectangles
• Relationships are drawn as diamonds with lines connecting the entities involved in relationships
• Composite entity: exists to implement a many-to-many relationship
• Existence dependency: existence of one entity depends on the existence of another related entity
• Weak entity: depends on another entity for its own existence
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- getting data into r commander university of california
- hands on lab building your first extract transform load
- rstudio 01
- extremely short introduction to r
- r piesales fordham
- custom web pages color changes column names
- getting data into r commander donald bren school of
- texas a m university
- a short introduction to the r programming language for
Related searches
- texas a m money education center
- texas a m grading scale
- texas a m 2020 2021 academic schedule
- texas a m extension child care training
- texas a m academic calendar
- texas a m 1098 t
- texas a m campuses in texas
- texas a m financial aid portal
- texas a m university campuses
- texas a m online engineering degree
- texas a m online engineering masters
- texas a m college station tuition