Assignment 1: Database Model

FIND A SOLUTION AT Academic Writers Bay

1Database Design 5915 and Database Design G 6672Assignment 1: Database ModelThe purpose of assignment is to provide you with experience in analysing and designing a database for a givenproblem. It will help you to understand the nature and purpose of database analysis and design.Assessment BriefRead the Canberra Driving School scenario information below and solve the problem. To do this you are requiredto;1. Identify entity types and their attributes, including the primary keys (in third normal form).Please state all assumptions that you have made.(30 Marks)2. Compile an E-R diagram for the above scenario based on your solution part (1) above.(20 Marks)Note: If you make any assumptions, they should be explained clearly. Submit the list of your entities andattributes including primary keys of your solution (in a MS Word / pdf document) and an E-R diagram asan image (jpg/jpeg/png format).Please note that list of entities should be in the following format and identify the primary key, foreignkey appropriately:NextOfKin (NextOfKinNo (PK), firstName, lastName, contactTelNo, streetNo, streetName, suburb, postcode, city (FK))MarksThis assignment is marked out of 50 marks and is worth 25% of the overall grade.FormatYour response task should be prepared using font size 11-12, portrait orientation, and narrow marginsif you use Microsoft Word or similar settings if you use other editing software. ERD could be in E-R diagramas an image (jpg/jpeg/png format).SubmissionThis assignment is an individual assignment. There are no restrictions on the use of word processors orsimilar tools to produce submissions for this assignment.2Be sure to maintain regular back-ups for any models or material prepared with the aid of software. Lossof files will not be accepted as an excuse for non-completion of this assignment.Submit your assignment to the ‘Assignment 1 submission box’ in Canvas, including; a cover sheet and aword document including your list of entities and the E-R diagram.Weighting: 25% of the overall grade Due date: Friday, 17 September, 11:59pmScenario: Canberra Driving SchoolCanberra Driving School (CDS) was established recently. CDS has five branches in Canberra. CDS requiresyour team to design a database system to enable more smooth operation of the centre. The details of thebranches of CDS are:
Branch name
CDS Downer
50 Cadell St, Downer, 2602, ACT
CDS Phillip
12 Athllon Dr, Phillip, 2606, ACT
CDS Belconnen
390 Benjamin Way, Belconnen, 2617, ACT
CDS Civic
12 Alinga St, 2601, City, ACT
CDS Bonner
61 Mabo Blvd, 2914, Bonner, ACT
CDS database will record and store the data about each of its clients and instructors including their firstand last name, postal address, and home address (street number, street name, suburb, post-code, city),sex and type of driving classes a client is enrolled in. The name (first and last name), contact telephonenumber and address (street number, street name, suburb, post-code, city) of client’s next-of-kin is alsostored in database.CDS offers several types of driving classes. Driving classes are managed under course development sectionof CDS. There exist several types of driving classes. These are namely Class A (Trucks), Class B (Mini Trucks)and Class C (normal passenger vehicles). A client can be enrolled in up to two different classes at any time.Each class type has different fees. CDS database will record and store the data about each of its classes.Clients need to book for each class up to two weeks beforehand. The timetable and availability of eachinstructor is provided on CDS website.3When a client enrols at CDS he or she is assigned a client number and his/her details are recorded. Everyclient is provided with a client number and an ID-card as well as the rules of CDS and charges. Each classtype has its own terms and conditions. A client can use his/her client number to book a class (using theonline booking facilities).CDS has several staff members in each branch. For each staff member the following data is stored in thedatabase system: staff first and last name, staff number, position, sex, date of birth, name of the sectionand branch number he/she works in as well as each staff internal telephone number. Each branch has amanager. Detail of each manager for each branch is also stored in CDS database.CDS has also several instructors in each branch. For each instructor the following data is stored in the CDSdatabase system: staff first and last name, instructor number, position, sex, date of birth, type of drivingclasses that an instructor can teach (i.e., Class A, B or C), internal telephone number and branch number.CDS consists of four sections. These sections are: instructor section, information and course developmentsection, assessment and enrolment section, administration section. The information about each sectionis stored in the CDS database. The information about each section includes the section name, location,telephone and fax number. CDS clients can browse the CDS website to find out the details of all classesavailable for each week.Instructors belong to the instructor section. Each instructor is limited to teach a maximum of two typesof classes at any day. The instructor’s identity is established with their ID number and ID-card and it isused to retrieve the details of the classes that they teach. The details of each class (class number,Instructor ID number, date and time, details of the clients for each instructor) are stored in the CDSdatabase. If a staff, instructor, or a client loses his/her ID-card, he/she then contacts the CDS at thefollowing telephone number: 188473635. The staff at administration section once notified will deactivatethe lost ID-card and a new ID-card is issued later. Issue of a new ID-card may take up to 24 hours. When anew card is issued the details of the staff in administration section that issued the card is recorded in CDS.The details of all lost card stored in CDS database is: first name and last name of staff who issued the newID-card, date, and time that the card was issued and staff first and last name of the staff that has losthis/her card.When a client is enrolled in a driving class, then the client should make a payment for his/her classesbefore he/she can take that class. The amount of payment depends on the type of class. The payment canbe performed via the CDS website using a credit card. All payment details made by all clients for each classenrolment is stored in CDS database. The details of payments stored are payment number, client first andlast name, date of payment, amount paid, class number, class date, class time, instructor number. Foreach payment made by a client a receipt is issued to the client. Receipt details are, the client first and lastname, receipt number, date of payment, amount paid and payment number. Receipt details are stored inCDS database.4After each class, the instructor records the progress made by the client and notes the mileage of the carused for the driving class before and after the class. Vehicle details used for each class and their millageis stored in CDS database. These details are: vehicle VIN number, registration number, type, make andmodel of vehicle, class number, client and instructor first and last name. CDS has a pool of cars and trucksthat are adapted for teaching.CDS website also provides a class timetable and instructor availability facilities where a client can checkthe availability of a certain class with the details of the instructor teaching the class. The timetable ofclasses is stored in CDS database.CDS timetable of classes is:
Monday Tuesday Wednesday Thursday Friday Saturday9:30–10:30amClass A,Instructor: JimDouglas9:30–10:30amClass B,Instructor: JimDouglas9:30–10:30amClass A,Instructor: JimDouglas9:30–10:30amClass B,Instructor: JimDouglas9:30–10:30amClass A,Instructor: JimDouglas9:30–10:30amClass B,Instructor: JimDouglas10:30–11:30amClass B,Instructor:Tom10:30–11:30amClass B,Instructor:Tom10:30–11:30amClass B,Instructor:Tom10:30–11:30amClass B,Instructor:Tom10:30–11:30amClass B,Instructor:Tom10:30–11:30amClass B,Instructor:Tom1:30-2:30pmClass CInstructor:Jane Pontiac1:30-2:30pmClass BInstructor:Jane Pontiac1:30-2:30pmClass CInstructor:Jane Pontiac1:30-2:30pmClass BInstructor:Jane Pontiac1:30-2:30pmClass CInstructor:Jane Pontiac1:30-2:30pmClass BInstructor:Jane Pontiac3:30-4:30pmClass AInstructor:Jack Peace3:30-4:30pmClass BInstructor: JackPeace3:30-4:30pmClass BInstructor: JackPeace3:30-4:30pmClass AInstructor: JackPeace3:30-4:30pmClass BInstructor: JackPeace3:30-4:30pmClass AInstructor:Jack Peace5:30-6:30pmClass BInstructor:Charlie Marek5:30-6:30pmClass CInstructor:Charlie Marek5:30-6:30pmClass BInstructor:Charlie Marek5:30-6:30pmClass CInstructor:Charlie Marek5:30-6:30pmClass CInstructor:Charlie Marek5:30-6:30pmClass BInstructor:Charlie Marek
Class timetable details are stored in CDS database. CDS keeps track of maintenance schedules for each oftheir vehicles. CDS asks their instructors to record their names and mileage of the vehicle they used foreach class at the end of each class. Each vehicle is serviced every 5000 kilometres or every three months.The details of all vehicles and their service history are recorded. These details are available to all staff fromthe administration section only. The vehicle service detail stored in CDS database is: Vehicle number, dateof service, type of service and the details of the vehicle dealership in which each vehicle is serviced.5In addition, CDS keeps the information about each of the vehicles (i.e. service history of each vehicle andthe amounts of money paid for services and repair description for taxation purposes). To service vehicles,CDS has a service agreement with three local vehicle dealerships who provide servicing of all vehicles at avery competitive rate.CDS details stored about a local car dealership that they work with are stored in CDS database. Thesedetails are: car dealer name, dealer address, telephone number, email address.The details of petrol stations that provide petrol to CDS are store in CDS database. These details are: petrolstation name, address, telephone number, email address. CDS provides their instructors with industryrelated courses. Up to three courses are provided for each instructor every six months. Each instructor isrequired to attend all courses. The details of all courses attended by each instructor are stored in CDSdatabase. These details are: instructor first and last name, course name, course number and date ofcourse attendance by each instructor for each course. For each course, the course name, course number,course duration and the date the course is offered is recorded in CDS database.Instructors are required to sit for instructor examinations every three years to renew their instructordriving licence. Details of instructor licence examinations are recorded in CDS database. These details are:instructor first and last name, instructor number, driving examination date and the result of examination(pass or fail). CDS has two seminar rooms. CDS clients can book seminar rooms. Details of CDS customersthat book seminar rooms are stored in CDS database. These details are: customer number, customer firstand last name, seminar room number and date and time for which a seminar room is booked.Details of staff that book a seminar room for a customer is also recorded in CDS database. These detailsare: staff number, staff first and last name, the position of staff, date and time for which a seminar roomis booked. Hiring of seminar rooms costs $130 per day for customers. Customers need to make a paymentfor any seminar room that they book. Details of all seminar booking, and payments are stored in CDSdatabase.6Marking criteria
25.5-30 marksHD
22.5 – 25.5marksDI
19.5 – 22.5 marksCR
15 – 19.5 marksP
< 15marksF
Identify entitytypes,attributes, andthe primary keysand foreign key
Accuratelyidentifies allentity types,their attributes,and primary keysand foreign keyin the thirdnormal form.Comprehensivelyexplainsassumptionsmade.
Accuratelyidentifiesalmost entitytypes, theirattributes, andprimary keysand foreign inthe thirdnormal form.Explainsassumptionsmade.
Identifies themain entitytypes, theirattributes, andprimary keysand foreign keyin the thirdnormal form.Identifiesassumptionsmade.
Identifies mostof the entitytypes, theirattributes, andprimary keys –mostly presentlyin the thirdnormal form.Lists someassumptionsmade.
Identifies veryfew entity types,there andprimary keysand foreignkeys. Does notpresent in thethird normalform.Noassumptionslisted.
17-20 marksHD
15- 17 marksDI
13 – 15 marksCR
10 – 13 marksP
< 10marksF
Compile an E-Rdiagram
A comprehensiveE-R diagram usescorrect symbolsto illustrateentities,relationships,and multiplicitiesrepresenting arelationaldatabase model.
E-R diagramuses correctsymbols toillustrateentities,relationships,andmultiplicitiesrepresentinga relationaldatabasemodel.
E-R diagramuses correctsymbols toillustrateentities,relationships,andmultiplicitiesas a goodattempt torepresent arelationaldatabasemodel.
E-R diagramuses symbols toillustrateentities,relationships,andmultiplicity.
Insufficient E-Rdiagram toillustrateentities,relationships,andmultiplicities.

YOU MAY ALSO READ ...  Stephen plans to purchase a car
Order from Academic Writers Bay
Best Custom Essay Writing Services