Create a databse and all the required tables using mysql or SQL server with the
ID: 3713272 • Letter: C
Question
Create a databse and all the required tables using mysql or SQL server with the following requirement and insert data into the tables:
The owners of a small computer repair shop would like to keep track of the repair jobs for computers they repair, the items used for each repair job, the labor costs for each repair job, the repairmen performing each repair job, and the total cost of each repair job.
When customers bring their computers in to be repaired, they make a deposit on the repair job and are given a date to return and pickup their computer. Repairmen then perform repairs on the customers’ computers based on the repair job, and detail the labor costs and the items used for each repair job.
When customers return they pay the total cost of the repair job less the deposit, collect a receipt for their payment, and pickup the repaired computer using this payment receipt.
Explanation / Answer
From the above statements:
Tables needed:
Customer
CustomerId , LaborJobId, Deposit, ReturnDate, ItemDeposited
RepairJob
RepairJobId , Item , LaborId
Labor
LaborId , details , cost
Payment
PayId,CustomerId ,LaborJobId, amountLeft , pickupDate
The schema can be designed as:
CREATE table Labor ( LaborId Integer Primary Key, details varchar(30), cost decimal);
CREATE table LaborJob (LaborJobId Integer Primary Key, Item varchar(10), laborId FOREIGN KEY REFERENCES Labor (LaborId ));
CREATE table Customer(CustomerId integer Primary Key, LaborJobId FOREIGN KEY REFERENCES LaborJob (LaborJobId ) , Deposit decimal, ReturnDate Date, ItemDeposited varchar(10));
CREATE table Payment(PayId integer Primary Key,CustomerId FOREIGN KEY REFERENCES Customer(CustomerId ) ,LaborJobId FOREIGN KEY REFERENCES LaborJob (LaborJobId ), amountLeft decimal , pickupDate Date);
UPVOTE!
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.