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.

Google Online Preview   Download