MITS4003 Database Systems VIT
This assessment item relates to the unit learning outcomes as in the unit descriptor. This assessment is designed to improve students’ skills to analyze organization database requirements, develop a data model to reflect the organization’s business rules. This assessment covers the following LOs.
- Synthesize user requirements/inputs and analyse the matching data processing needs, demonstrating adaptability to changing circumstances;
- Develop an enterprise data model that reflects the organization’s fundamental business rules; refine the conceptual data model, including all entities, relationships, attributes, and business rules.
- Derive a physical design from the logical design taking into account application, hardware, operating system, and data communications networks requirements; further use of data manipulation language to query, update, and manage a database.
Prestige Automobile Rental (PAR) is a vehicle rental company that rents old vehicles to the public. PAR has been using manual methods for keeping track of their customers and their rentals. However, the company would now like to go online and allow customers to search the available vehicles and see their rental history.
For the first time, when the customer rents a vehicle from PAR their details (name, address, phone number, driving license number and credit card number) are recorded. The date they become a customer is also stored. There are certain types of vehicles which are classified as heavy vehicles. It is a policy of PAR that these vehicles are only rented to customers who have no demerit points on their driving license.
The information about the vehicle (type, registration number, year/make/model, vin number, distance travelled and current condition) are also stored. Each vehicle has a unique ID. The customer can search for their desired vehicle and can see if it is available.
All rentals are for 7 days. Rental charges are based on the type of vehicle (Car (C) or Heavy Duty (HD)). Rental must be paid for on collection. Customers can rent up to 2 vehicles at a time. Each rentalID is for a single vehicle for one customer. (The rentalID is an auto number). When the rental is taken out the date of checkout is recorded along with calculated due date (7 days from checkout date). When the rental is returned, the date is recorded in the returned date.
PAR would like to store ‘demerit’ points for the renter in order to identify bad renters. These demerit points are accumulated at a rate of one point per day a rental is overdue. PAR will cancel the membership of the customers who have too many demerit points.
Assignment Requirements and Deliverables:
Part A – 10%
Submitted as a MS Word Document
- Develop an Entity Relationship Diagram
- Relational Schema (including Primary, Foreign Keys and all attributes).
- Supplementary Design requirements (data attribute information)
- Discuss physical design and any assumptions made during design
This assessment item relates to the unit learning outcomes as in the unit descriptor. This assessment is designed to improve students’ skills to analyz organization database requirements, normalize the database and use data manipulation language to create, update and manage database. Furthermore their research skill will be analyzed and application on the given case study. Thi assessment covers the following LOs.
- Derive a physical design from the logical design taking into account application hardware, operating system, and data communications network requirements; further use of data manipulation language to query, update, an manage a database.
- Identify functional dependencies, referential integrity, data integrity an security requirements; Further integrate and merge physical design by applyin normalization techniques.
- Design and build a database system using the knowledge acquired in the uni as well as through further research on recent trends to demonstrate competence in various advanced tasks with regard to modelling, designing, and implementing a DBMS including Data warehousing, Data Management, DB Security.
Please note: Part B is based on Part A. Assignment Requirements and Deliverables:
Part B – 10%
Submit a single plain text file with filename as “studentNo_studentName_SQL” containing all SQL implementation. Your part B must work on SQL database and be able to be demonstrated
SQL code required (based on Part A):
- CREATE TABLE statements for all tables in your ERD (Part A) including integrity constraints.
- INSERT INTO statements for populating the database o You must at least enter 5 customer details.
- You must at least put 10 vehicle details
- Create at least three rentals with different customers
- Data may need to be inserted in a particular order to comply with integrity constraints.
- SELECT statement that will return all vehicle that have not been rented
- The details to be included (Vehicle ID, type, registration number, year/make/model, vin number)
- SELECT statement that will produce the total amount paid per customer
- The details to be included (customerID, customer name (first and last combined), phone number and total amount
Research and Discussion
Submitted as a MS Word Document
- Discuss what is normalization including the normal form of each entity in your design and why that is optimal. Also discuss how normalization was achieved for each entity. Your entities must be in 3NF unless there is a compelling reason provided to keep a relation in 2NF.
- Discuss the database security and control measures required in the above case study. Also discuss the commands used by SQL server to enforce those security controls. Elaborate the data integrity problems you encounter during concurrent user accesses and technique of sharing and recovery.