Please see the attached file for the actual assignment. It has the images.
The following ER Diagram represents the data model for a maritime system that will track the movements of ships visiting ports and their home ports
(1) Create a relational schema . (listing of table structure with attributes that includes specification of keys and lines+arrows addressing foreign keys) for this data model
(2) Write the create table statements in SQL with propto1 er syntax 5 points] (make assumptions about data types, make sure to include keys/foreign keys, think carefully about how to handle the weak entity and the many many relationship)
(3) Provide a definition or answer:
(A) From a database perspective, data can be classified into two types: atomic and compound. a. Define atomic b. Define compound
(B) If a relation A has 4 attributes (hence degree 4) and 7 tuples, and relation B has 3 attributes and 5 tuples what is the degree and size of A X B?
(C) Define Primary Key:
(D) Define Foreign Key:
(4) (10 points) For the relation:
What is a valid (composite, candidate) key?
Explain in writing why it is a valid key and why other choices are not:
(5) (10 pts) Describe at least 3 different type of JOINs and how they differ.
(6) (15 pts) Given relation Table1(A,B,C): And relation Table2(A,B,C): 4 (A) Provide the relation for Table1 ∩ Table2: 4 (B) Provide the relation for Table1 – Table2: 4 (C) Compute (Table1 – Table2) (Table2 – Table1), often called the “symmetric difference” of Table1 and Table2. A B C 1 2 3 4 2 3 4 5 6 2 5 3 1 2 6 A B C 2 5 3 2 5 4 4 5 6 1 2 3 5) (8 pts) Based on the tables below, provide the relation for TableB ÷ TableA: TableA PNO Desc 8 Rachet & Socket 10 Hammer 14 Screwdriver TableB Emp PNO Desc John 8 Rachet & Socket John 10 Hammer Bill 14 Screwdriver Mary 10 Hammer Mary 14 Screwdriver Bill 8 Rachet & Socket John 14 Screwdriver Mary 12 Saw 6) (1 pts ) Write a series of relational algebra statements 2 pizza SQL database we looked at that that based on the relations in will implement: “Find the names of all people who eat at least one pizza served by the Little Caesars not frequent Little Caesar s . ” but who do not frequent Little Caesar s . ”