Question

This is about database system. Thank you.

Question B1 Create a Crow's Foot ERD with the business rules described below. Write all appropriate connectivities and cardinalities in the ERD. A music store would like to develop a database to manage information about the CDs, or vinyl collection, the customers and transactions information. It stores the relationships between artists, albums, tracks, customers and transactions. Here is the list of requirements for the database: The collection consists of albums. An album is made by exactly one artist. An artist makes one or more albums. An album contains one or more tracks Artists, albums, and tracks each have a name. Each track is on exactly one album. Each track has a time length, measured in seconds. A customer can buy more than one product at each purchase and the information is recorded in an invoice. Each invoice should record the date, the quantity of each product and the total amount of each purchase. There is no requirement to capture composers, group members or sidemen, recording date or location, the source media, or any other details of artists, albums, or tracks. The attributes are straightforward: artists, albums, tracks and customers have names, as well as identifiers to uniquely identify each entity. Each invoice has a unique identifier. (25 marks)

Answer 1

SOLUTION

Question B1

ENTITY 1	ENTITY 2	RELATIONSHIP TYPE
Collections	Albums	One-to-Many
Artists	Albums	One-to-Many
Albums	Tracks	One-to-Many
Order	Customers	Many-to-One
Invoice	Albums	Many-to-One

All Access ... Relationships X Search... Tables Tracks Customer Collections Albums Artists 7 CollectionID CollectionName 00 TrackID TrackName AlbumID TimeLength 1 Customer CustomerName CustomerAddress CustomerPhone CustomerEmail Collections BE Customer Invoice BE Order FEE Tracks Albums Order OrderID CustomerID AlbumID AlbumName CollectionsID 81 8L Artistid Invoice Artists 00 Artisti ArtistName 1 Invoices Ordered AlbumID Quantity TotalAmount InvoiceDate

Question B2

a) RELATIONSHIP TYPES

ENTITY 1	ENTITY 2	RELATIONSHIP TYPE
Model	Vehicle	One-to-Many
Vehicle	Forwarding_Schedule	One-to-Many
Forwarding_Schedule	Driver	Many-to-One

b) BUSINESS RULES

· There can be more there on vehicle belonging to the same model

· There can be more than one Schedule for the same Vehicle

· One Driver can be associated with more than one Forwarding Schedule

· One Vehicle can have only one Driver per schedule.

c) Entity Integrity & Referential Integrity in Vehicle Table

· Vehicle Table exhibits entity integrity since every record in Vehicle table can be uniquely identified by the Vehicle_Code attribute/column of the Vehicle table.

· Vehicle Table exhibits refertial integrity since every value in the Model_Code attribute/column has a matching reference value in Model Table Model_Code attribute/column.

d) SQL Statement to display Driver Names assigned to deliver after 2:00 PM on July 12, 2019.

SELECT Driver.LastName, Driver.FirstName

FROM Driver INNER JOIN Forwarding_Schedule ON Driver.[Driver_ID] = Forwarding_Schedule.[Driver_ID]

WHERE (((Forwarding_Schedule.Delivery_Date)>=#7/12/2019 14:0:0#));

e) SQL Statement for License Plate No of Vehicles available between dates

SELECT Vehicle.License_Plate_No

FROM Vehicle

WHERE Vehicle.License_Plate_No NOT IN (SELECT Vehicle.License_Plate_No

FROM Vehicle INNER JOIN Forwarding_Schedule ON Vehicle.[Vehicle_No] = Forwarding_Schedule.[Vehicle_No]

WHERE (((Forwarding_Schedule.Delivery_Date) BETWEEN #7/26/2019 11:0:0# AND #7/26/2019 17:0:0#)));

All Access Forwarding Schedule Query X Search... Tables Driver Forwarding Schedule Х LastName First Name Lee Alan Chan John Lee Alan Cheung Kanny Costella Kenneth Srivastava Jardi Chan John Forwarding_Schedule Model Vehicle Queries Forwarding Schedule Q... Х All Access ... Forwarding_Schedule Forwarding Schedule Query X SELECT Driver.LastName, Driver. FirstName FROM Driver INNER JOIN Forwarding Schedule On Driver.[Driver_ID] = Forwarding Schedule.[Driver_ID] WHERE (((Forwarding Schedule.Delivery_Date)>=#7/12/2019 14:0:0#)); Search... Tables Driver Forwarding Schedule Model

All Access ... Search... Vehicle Query X SELECT Vehicle.License_Plate_No FROM Vehicle WHERE Vehicle.License_Plate_No NOT IN(SELECT Vehicle.License_Plate_No FROM Vehicle INNER JOIN Forwarding Schedule ON Vehicle.[Vehicle_No] = Forwarding Schedule. [Vehicle_No] WHERE (((Forwarding Schedule.Delivery_Date) BETWEEN #7/26/2019 11:0:0# AND #7/26/2019 17:0:0#))); Tables Driver Forwarding Schedule Model BE Vehicle Queries Forwarding_Schedule Q... Vehicle Query All Access ... Search... Tables Driver Vehicle Query License_Platı - AB1267 CK684 HJ2293 KK9662 MT329 PE3331 RW3328 SB6871 Forwarding Schedule Model Vehicle Queries Forwarding Schedule Q... Vehicle Query

All Access Vehicle Query Х Relationships X Search... O Tables Driver Model Vehicle Forwarding Schedule Forwarding Schedule Driver Model 1 Model Code Manufacturer Model Name 00 1 Vehicle_No Model Code License_Plate_No Vehicle Driver_ID LastName FirstName Address Queries 2 Driver_ID Vehicle_No From To Delivery_Date Duration Forwarding Schedule Q... Vehicle Query

Hence the Solution..!