COSC 341 Midterm Exam Part B NAME: _________________________

Spring 2023

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[15 points]

(2) Write the create table statements in SQL with proper syntax (make assumptions about data types,

make sure to include keys/foreign keys, think carefully about how to handle the weak entity and the

many-to-many relationship) [20 points]

SHIP

PORT

Home

Port

Visits

es

Country Name

Ship Name Owner Tonnage

Port Name

Arrive Date

Depart Date

Tonnage

N

M

(3) (10 pts) 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:

(5) (10 pts) Describe at least 3 different type of JOINs and how they differ.

A B C

a1 b1 c1

a1 b2 c1

a1 b2 c2

a1 b3 c3

a1 b3 c4

a2 b4 c5

a2 b5 c5

What is a valid (composite, candidate) key?

Explain in writing why it is a valid key and why other choices are not:

What functional dependencies (between A, B, C) are implied in rows:

(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) (12 pts) Write a series of relational algebra statements that based on the relations in the pizza SQL database we looked at that will implement:

“Find the names of all people who eat at least one pizza served by Little Caesars but who do

not frequent Little Caesars.”