Assignment 2
Due date: Monday, September 30, 11:59PM.
NOTES:
- 10% penalty for each day late, up to three days
- This is an individual assignment; no groups allowed
This assignment is meant to give you practice with the SQL DDL.
Student Equipment Loans
The students at the University Moon Light are offered the ability to loan electronic equipment for class work, such as Tablets, Laptops, monitors, projectors, etc. Below is the schema used by the loan center to record the student loans.
Database EquipmentLoans
Students(SID, FNama, LName, Dept_Name, Email)
Equipment(EID, TCode, TypeID, Location)
TypeID is FK references EquipmentTypes
Loans(SID, EID, CheckOutDate, CheckOutTime, CheckInTime, CheckedOutBySID)
SID is FK references Students
EID is FK references Equipment
CheckedOutBySID is FK references Students
EquipmentTypes(TypeID, Name, Description, LoanRateHour)
Tasks
- Create Database
- Write SQL DDL statements to create the database along with all its tables.
- You need to pick the appropriate attribute data type for each attribute.
- The statements must include all integrity constraints.
- Alter Database
- Modify the data type of LoanRateHour to be NUMERIC (4, 2).
- Rename the field CheckedOutBySID to StudentOnDuty.
- Introduce ON DELETE CASCADE on CheckedOutBySID.
- Make (FNama, LName, Dept_Name) a Unique Key in Stundets.
- Introduce the constraint that CheckOutTime < CheckInTime.
- Introduce the constraint that SID <> CheckedOutBySID.
- Rename the table Loans to StundetLoans.
- Use Insert statement to:
- Insert at least 10 students
- Insert at least 10 records in Equipment.
- Insert at least 20 loans
- Insert at 5 types of equipment.
- Delete Database
- Delete the content of all tables.
- Delete the tables themselves.
Deliverables
- You have to produce a SQL script that accomplishes all the above steps. For each of the above items, there must be a corresponding set of statements in your script. Introduce comments in your script to indicate which item your SQL statements address.
- A document containing Picture Screens captures from your computer showing the successful execution of each item in A), followed by those in B) followed by those in C).