Reference no: EM132644937

MITS4003 Database Systems - Victorian Institute of Technology

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, 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. This assessment covers the following LOs.

1. 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

2. Identify functional dependencies, referential integrity, data integrity and security requirements; Further integrate and merge physical design by applying normalization techniques.

3. Design and build a database system using the knowledge acquired in the unit 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.

Part B -

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 Assignment 1):
• CREATE TABLE statements for all tables in your ERD (Assignment 1) including integrity constraints.
• INSERT INTO statements for populating the database
o You must at least enter 5 customer details.
o You must at least put 10 vehicle details
o Create at least three rentals with different customers

o 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
o 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
o 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.

Attachment:- Database Systems.rar