Geog 495: GIS Database Design
10/3/05
Relational database model
How data are represented in relational database model
Entity (set) is seen as a table, and DB is composed of relationships between entity (set)
Table = a group of related entities (or entity set)
Row = entity
Column = attribute (characteristic) of entity; one column has to conform to the same type
Cell = data value
Key
What is a key?
- Primary key: uniquely identifies an entity; should be Not Null
- Foreign key: allows for linking one table to the other table; matches the primary key value in a table to which it is related
How do you know which is primary key? (hint: functional dependence)
Keys can be composite (concatenated value becomes an unique identifier; e.g. First Name + Last Name)
Secondary key: STU_NUM is not intuitive, STU_LNAME+STU_FNAME can be alternatively used for retrieval purpose
Integrity rules
- Entity integrity
- Referential integrity
Relationships
Understand notation schemes of Entity-Relationship Diagram (ERD):
- Chen Model
- Crow’s Foot Model
Different types of relationships
- 1:1
- 1:M
- M:N
à Redundancy occurs
Example: Transforming M:N to 1:M by a linking table
So we have a new db design scheme that serves to reduce data redundancy
Index
Allows for logically accessing rows in a table (e.g. autonumber in MS-Access)
Lab
Open file Ch03_StoreCo.mdb at P:\geog495aut05
1. For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None in the assigned space.
|
TABLE |
PRIMARY KEY |
FOREIGN KEY(S) |
|
EMPLOYEE |
|
|
|
STORE |
|
|
|
REGION |
|
|
2. Do the tables exhibit entity integrity? Answer Yes or No, and then explain your answer.
|
TABLE |
ENTITY INTEGRITY |
EXPLANATION |
|
EMPLOYEE |
|
|
|
STORE |
|
|
|
REGION |
|
|
3. Do the tables exhibit referential integrity? Answer Yes or No, and then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key.
|
TABLE |
REFERENTIAL INTEGRITY |
EXPLANATION |
|
EMPLOYEE |
|
|
|
STORE |
|
|
|
REGION |
|
|
4. Describe the type(s) of relationship(s) between STORE and REGION.
5. Draw the entity relationship diagram for the relationship between STORE and REGION.
6. Describe the type(s) of relationship(s) between EMPLOYEE and STORE. (Hint: Each store employs many employees, one of whom manages the store.) Draw the entity relationship diagram to show the relationships among EMPLOYEE, STORE, and REGION.