Information Systems
Assignment # 3
1a) List the columns of the two tables.
Students (Student ID, firstName, lastName, Course ID)
Courses (Course ID, CourseName)
b) For each of the tables you created, what are the candidate keys?
There are Students Table and Courses Table.
Students Table: Candidate keys include student ID, firstName and lastName.
Courses Table: They candidate key is the Course ID and courseName.
c) For each of the tables you created, what is the primary key?
The primary key of Students Table is Student ID.
The primary key of Courses Table is Course ID.
d) Explain how the two tables are related. Which table contains the foreign key? What is the foreign key?
The students and their course names make the tables related. The student ID correlates to the students' last names and first names and the course ID. Therefore, this is the primary key. The course ID is the foreign key of the Course Table.
2) The Cape Codd Outdoor Sports database has the following tables:
RETAIL_ORDER (OrderNumber, StoreNumber, StoreZip, OrderMonth, OrderYear, OrderTotal)
ORDER_ITEM (OrderNumber, SKU, Quantity, Price, ExtendedPrice)
SKU_DATA (SKU, SKU_Description, Department, Buyer)
WAREHOUSE (WarehouseID, WarehouseCity, WarehouseState, Manager, Squarefeet)
INVENTORY (WarehouseID, SKU, SKU_Description, QuantityOnHand, QuantityOnOrder)
a) There is a flaw in the design of the INVENTORY table. Explain what this design flaw is.
There are 2 primary keys in the inventory table, which are the Warehouse ID and the SKU. They are primary key references for relationships to other tables.
b) For each of the tables above, what is the primary key?
Retail_Order: Order Number
Order_Item: Order Number
SKU_Data: SKU
Warehouse: Warehouse ID
Inventory: Warehouse ID and SKU
c) Which tables contain foreign keys? What are the foreign keys?
Order_Item table contains a foreign key and the foreign keys are the SKU attribute.
3) We know that if a table
1a) List the columns of the two tables.
Students (Student ID, firstName, lastName, Course ID)
Courses (Course ID, CourseName)
b) For each of the tables you created, what are the candidate keys?
There are Students Table and Courses Table.
Students Table: Candidate keys include student ID, firstName and lastName.
Courses Table: They candidate key is the Course ID and courseName.
c) For each of the tables you created, what is the primary key?
The primary key of Students Table is Student ID.
The primary key of Courses Table is Course ID.
d) Explain how the two tables are related. Which table contains the foreign key? What is the foreign key?
The students and their course names make the tables related. The student ID correlates to the students' last names and first names and the course ID. Therefore, this is the primary key. The course ID is the foreign key of the Course Table.
2) The Cape Codd Outdoor Sports database has the following tables:
RETAIL_ORDER (OrderNumber, StoreNumber, StoreZip, OrderMonth, OrderYear, OrderTotal)
ORDER_ITEM (OrderNumber, SKU, Quantity, Price, ExtendedPrice)
SKU_DATA (SKU, SKU_Description, Department, Buyer)
WAREHOUSE (WarehouseID, WarehouseCity, WarehouseState, Manager, Squarefeet)
INVENTORY (WarehouseID, SKU, SKU_Description, QuantityOnHand, QuantityOnOrder)
a) There is a flaw in the design of the INVENTORY table. Explain what this design flaw is.
There are 2 primary keys in the inventory table, which are the Warehouse ID and the SKU. They are primary key references for relationships to other tables.
b) For each of the tables above, what is the primary key?
Retail_Order: Order Number
Order_Item: Order Number
SKU_Data: SKU
Warehouse: Warehouse ID
Inventory: Warehouse ID and SKU
c) Which tables contain foreign keys? What are the foreign keys?
Order_Item table contains a foreign key and the foreign keys are the SKU attribute.
3) We know that if a table