please go through the marking guide
MITS4003 Database Systems Assignment 1 August 2020 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 and use data manipulation language to manage database. This assessment covers the following LOs. 1. Synthesize user requirements/inputs and analyse the matching data processing needs, demonstrating adaptability to changing circumstances; 2. 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. 3. Derive a physical design from the logical design taking into account application, hardware, operating system, and data communications networks requirements; 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 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% (Due in week 5) 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 Marking Guide: Marking Guide: Unacceptable Acceptable Good Excellent Entities, Primary Keys, Relationship Does not adequately have all required Entities, Primary Keys and Relationship in ER diagram Partially have all required Entities, Primary Keys and Relationship in ER diagram Most of the required Entities, Primary Keys and Relationship are there in ER diagram . All Entities, Primary Keys and Relationship are there in ER diagram Relational Schema Does not adequately have required tables, Primary Keys and Foreign keys. Partial have required tables, Primary Keys and Foreign keys. Most of the required tables, Primary Keys and Foreign keys are there. All tables, Primary Keys and Foreign keys are there. Supplementary Design Requirements Does not adequately have required attributes. Partially have required attributes. Mentioned most of the required attributes. All required attributes are there. Discussed physical design Adequately discussed about relationship in physical design. Partially discussed about relationship in physical design. Covered most of the relationship in discussion. Covered all relationship in discussion. MITS4003 Database Systems Assignment 2 August 2020 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 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. Please note: Assignment 2 is based on Assignment 1. Assignment Requirements and Deliverables: Part B - 10% (Due in week 9) 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. Marking Guide: Marking Guide: Case Study 1 Unacceptable Acceptable Good Excellent Create table statements Incorrect syntax, tables are not inline with Assignment 1 ERD. Partially correct syntax, tables are somewhat inline with Assignment 1 ERD. Syntactically correct and most tables are inline with Assignment 1 ERD. Code is correct and all tables are inline with Assignment 1 ERD. Insert Into statements Incorrect syntax Partially correct syntax and records are partially inserted as per the requirements Syntactically correct and most of the records are inserted as per the requirements. Code is correct and records are inserted as per the requirements. Select statements Incorrect syntax Partially correct syntax and gives output somewhat near to requirements. Syntactically correct and gives output very near to requirements. Code is correct and used LEFT JOIN, ISNULL, SUM, etc. and gives required output Normalization Not adequately discussed about normalization and how entities are in 3NF. Partially discussed about normalization and how entities are in 3NF. Most of the discussion was covered about normalization and how entities are in 3NF. Complete discussion is there about normalization and how entities are in 3NF. Database Security Not adequately discussed about security measures required and commands for enforce security controls. Also, did not elaborate data integrity problems encountered. Partially discussed about security measures required and commands for enforce security controls. Also, partially elaborate data integrity problems encountered. Covered most of the discussion about security measures required and commands for enforce security controls. Also, covered most of the integrity problems encountered. Covered completely discussion about security measures required and commands for enforce security controls. Also, covered all integrity problems encountered.