Reference no: EM132309721

Assignment

Design and implement appropriate data access, management and storage technologies to match the application domain.

REPORT Guidelines

Areportmust be submitted in a folder with the assignment cover sheet that is available at the reception. An electronic copy of your report must also be uploaded to the Assignment-2 Upload Link at the MOODLE. The report should contain the following:

1 A TITLE PAGE identifying the report title, the name and ID number of the student, date completed.

2 A TABLE OF CONTENTS identifying sections of the report as well as the corresponding page numbers.

3 An INTRODUCTION to the report.

4 A section on the PHYSICAL DESIGN that contains:

4.1 Use the current data volume details given below to modify the ER Diagram provided to create a data volume map.

a. The total number of current clients (drivers)is 7000

b. The total number ofadmin and office staffis 15

c. The total number of instructors including the manager is 5

d. The number of time slots per day is 13.

e. On average each client has been issued 3 certificates by instructors.

f. An instructor is available for 50% of the timeslots for next 30 days.

g. On average 75% of the available timeslots are booked by the clients for next 30 days.
 
4.2 Create the following tables of the database (see the ER Diagram provided) using Transact- SQL commands on the stu-mssql server. Specify primary keys and referential integrity constraints in your commands. If a table has foreign key(s), state the appropriate on-delete action.

Table structures are:
AdminStaff (Staff#, SName, SAddress, ContactNo)
PK- Staff#
Instructor (Ins#, IName, IAddress, ContactNo)
PK- ins#
Certificate (Cer#, Instructor_Comments, Date_issued, Cer_Type)
PK – Cer#
TimeSlot (Slt#, From, To)
PK – Slt#
Client (Clt#, CName, CAddress, ContactNo, Date_Joined))
PK – Clt#
Availability (Ins#, slt#, Date)
PK – (Ins#, slt#, Date) – composite key
FK – Ins#, and Slt#
Issue(Issue#, Cer#, Clt#, Ins#)
PK – Issue#,
FK -  Cer#, Clt#, and Ins#
Booking (Booking#, Date, Slt#, Ins#,Clt#)
PK - Booking#
FK - Slt#, Ins#, and Clt#

4.3 Populate tables with data (Enter at least five records to each table).
Include the INSERT SQL commands used to create tables in your report.

4.4 Create a Database Diagram with all the tables in it. Include a screen dump of the Database Diagram in your report. You must save the diagram of your database for the examiner to check.

4.5 Create index files to improve performance for appropriatetables. You may use the assumption that there should be at least 20000 records in a table to improve performance using an index file.

Include the CREATE INDEX SQL commands used to create index files in your report

4.6 A suggestion of one possible de-normalisation(merging tables)to improve performance of the database in the ER Diagram provided.

You must describeboth positive and negative consequences of the merge.Attributes of the merged tables must be shown using standard relational notations indicating primary keys and the foreignkeys in your report.

4.7 Create the following reports using the reports using the SQL Server 2014 Report Builder.

List of available instructors and their time slots of a particular day (say on the 1st of May 2019).

List of bookings of Instructors (name), Time slot (from, to) and the client (Cname)of a particular day (say on the 1st of May 2019).

You must state the name of your database for the examiner to check.

4.8 A Data Dictionary, containing details about each table for the case (e.g. field name, field description, data type, size, domain, range, example, required, indexed, primary key, foreign key, format and default value), organised for easy reference. The data dictionary must reflect the Final ERD.

Attachment:- Case Study- Driving Instruction Academy.rar