PART A
Objectives(s)
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 analyze 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.
Case Study:
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 that 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 traveled, and current condition) is 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 rental is for a single vehicle for one customer. (A rental is an auto number). When the rental is taken out the date of checkout is recorded along with the calculated due date (7 days from checkout date). When the rental is returned, the date is recorded in the returned data.
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 an 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
- My Best Assignment Help
PART B
Objectives(s)
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 the database. Furthermore, their research skill will be analyzed and applied to 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, and manage a database.
- Identify functional dependencies, referential integrity, data integrity, and security requirements; Further, integrate and merge physical design by applying 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 modeling, 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 the 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.
- A SELECT statement that will return all vehicle that has not been rented
- The details to be included (Vehicle ID, type, registration number, year/make/model, VIN number)
- A 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 the total amount
Research and Discussion
Submitted as an 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 the SQL servers to enforce those security controls. Elaborate on the data integrity problems you encounter during concurrent user accesses and the technique of sharing and recovery.
Visit At :- MITS5002 SOFTWARE ENGINEERING METHODOLOGY
