COIT20247 Database Design and Development Oz Assignments

COIT20247 Database Design and Development Oz Assignments

COIT20247 Database Design and Development Oz Assignments

Introduction of the subject:

Value: 35%

Part A. Database implementation using MS access (30 marks)

Assessment task:

Normalization

Map the ERD, with the help of sample question, within a group of relations in at least Third Normal Form (3NF). Make sure your relation should meet 3NF. Don’t show your working.

Choose two of your relation you want  from previous step, and perform the following those two relations:

Make a list of all the functional dependencies consisted in the public relationship.

Give a demonstration of relation meets Third Normal Form (3NF).

Relational database implementation:

Implementation of the ER model given, you have to fulfill the following task:

You have to form all the links in the Microsoft Access Database. Consider each one of them regarding in every table and create a suitable choices attributing data types & sizes, indexes, needed/not needed and validation rules. Your choices have to be relevant for each attribute and have to support data integrity.

NOTE: See the data integrity section below for specific data integrity requirements.

Create suitable relationship. Enforce referential integrity for all suitable relationship in the database. Cascade update and/or delete option have to be applied whenever necessary.

The default index formed by access of each table should be reviewed. Make sure that the indexes follow the guidelines provided in the textbook.

Fill the database with sample data of your own. You should contain adequate sample data to test your queries and report.

Data integrity

You need to apply integrity constraints into the database to define the following requirements:

1. The currency have to positive and expressed in 2 decimals only.
2. The start and end date of hire have to be later or equal.
3. Name of customer/employee should not be null.
4. Car’s registration number should contain 6 digits. First three characters being alphabets are must and rest should be numbers only.

Information requests:

Make queries to solve the following information requests.

NOTE: Don’t use the access query builder (QBE) to make your queries – you must type the problems manually using SQL view/editor.

The process of making the questions for information requests may also kept in the effectiveness of your SQL statements that is used in the quires. Your SQL statement should reflect the right business logic & SQL syntax is important.

Q1. Which cars have never been rented out? Make a list containing details of the car’s registration number, make and model, number of seats, manufacturing year, category  as well as the hire rate of daily basis.

Q2. Find out the customer who made booking for a minivan. This includes customer’s name, phone and the booking date?

 Q3. Find out the rental activities that have the most expensive cost? This will include customer’s name, phone and the booking date.

Q4. Find out the rental activities that have the most expensive cost? This will consist of customer name, car registration numbers, make and models, start hire dates, end hire dates and the costs.

Q5. Show specification of all rental activities. These details consist of customer name, pick up location, drop off location, start hire date end hire date and the cost.

Report:-

Produce a simple report object presenting the details of every rental activity presented. The detail should include customer first name, last name, start hire date, end hire date, and the cost. Present the average value of the cost of these rental activities must be showed in the end of the report.

Hints:

Form a query that contains a list of all the details required for a report. If you are not able to prepare this query, then form a report that is based on the table(s) directly.

NOTE:- you will not get full marks for the report if your base of the report is directly from the table(s) and some guidelines are not followed by you in your reports.

Take the help of a report wizard to create a basic report based upon your query [or the table(s) if you were unable to formulate the query].

Once the report wizard finishes, opt for design view and rename the labels appropriately; for example, change the labels ‘startDate’ to ‘Start Date’.

Part B: Database implementation using MySQL server (5 marks)

Include a easy version E-R model on the car rental activity, where you have to only face with three relational customer, Rental and car (ignore all other relations) related to E-R business model given for assignment. Using MySQL workbench 6.3 CE to produce a database with the above three relations, add denoting the primary keys, foreign keys and drawing an E-R diagram.   

Challenges student might face:

Several students may face the challenges in the completion of COIT20247 Database Design and Development assignment such as implementing database using MS Access, meeting the 3NF. With OZ Assignments, you get important academic guidance and tutorial help in COIT20247 Database Design and Development assignment help. Get feedback amendment without any additional cost.