| Attribute | SI | CO | SV | MV | FK | PK |
| PROD_ID | | X | | X | | X |
| PROD_CODE | X | | X | | | |
| PROD_NAME | X | | X | | | |
| PROD_STATE | X | | X | | | |
| PROD_DATE | | X | X | | | |
| PLANT_NUM | X | | X | | X | X |
| EMP_NUM | X | | X | | X | X |
2)
Use the following small database, composed of only two tables--AIRCRAFT and MODEL--to answer questions a through h.
a. How many tuples does the AIRCRAFT table have?Eleven
b. How many entity sets does this database contain?Two
c. How many records does the AIRCRAFT tabe have?Eleven
d. How many attributes does the MODEL table have?Eight
e. How many fields does the AIRCRAFT table have?Eight
f. Identify the AIRCRAFT tables's primary key?AC_NUM
g. Identify the AIRCRAFT tables's forgein key(s). note: If a table does not have a foreign key, write NONE.MOD_CODE
h. Identify the MODEL tables's foreign key(s). note: If a table does not have a foreign key, write NONE.NONE
3)
Given the small database in question 2, answer questions a and b.
a. Does the database exhibit referential integrity? Explain your answer briefly, but precisely.Yes, the FK's point to existing PK's of other tables, and therefore exibit referential integrity.
b. Does the AIRCRAFT table exhibit entity integrity? Why, or why not?Yes, the AIRCRAFT table meets the "match or null" requirements; and each row in the table is uniquely identified by the PK values in the table; therefore the table exhibits entity integrity.
4)
Using the Chen E-R methodology shown in the Rob/Semaan text, draw the fully-labeled ERD for the small database in question 2. Make sure you include all relationships, connectivities, and cardialities.
ERD
5)
Draw the fully-labeled relational schema for the design you produced in prob. 4
Schema
6)
Using the ERD methodology shown in the Rob/Semaan text, illustrate the supertype/subtype relationship between the entities EMPLOYEE and PILOT, using a fully-labeled E-R diagram. Show all connectivities and cardinalities.
Supertype
7)
What is the primary reason for establishing supertype/subtype relationships?
On a 1:1 relationship, one entity is related to only one other entity. Their features use an attribute as the PK, the other table's PK is also the FK to the Superset's table and neither table contain nulls. The Superset is the table that contains the commaon attributes, and the Subtype is the table containing the attributes only characteristic of that table.
8)
Why is the EMPLOYEE entity in problem 6 considered to be the supertype, while the PILOT entity is considered t be the subtype? Be brief, but pricise!Employee contains the common attributes EMP_LNAME, for the Superset; and the Subtype contains the attributes that are characteristic of PILOT's only.
9)
Entity relationship types are generally classified as _____, _____, and _____.1:1 or One to One, 1:M or One to Many, and M:N or Many to Many.
10)
Consider the information requirement that requires you o keep track of all employee educational qualifications. The following EMPLOYEE table segment shows a poorly implemented design. Using an ERD,design a database segment that eliminates the string storage shown in the following table structure and that makes it easy to track degrees, certifications, the dates on which these credentials were earned, and from what schools or programs those credentials were earned.Hint: You'll need three tables to do the job properly.
| EMP_NUM | DMP_LNAME | EMP_CERTIFICATION |
| 109 | Ratula | BBA, DBA, MS, Novell Admin, DB2, Oracle |
| 110 | Grateen | |
| 111 | Smith | BS, MS, Oracle |
| 112 | O'Donnel | BBA, MBA, Oracle, DB2, Access |
Tables
Return to index
Return to webproject
| |