A report must 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 eCampus. The report should contain the following:

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

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

• An INTRODUCTION to the report.

• 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.

1. The total number of student records is 50000
2. The number of students currently looking for jobs is 15000
3. The number of expected employer records – 1000
4. The number of location records – 50
5. Number of expected unfilled vacancies – 5000
6. Number of filled vacancies – 20000
7. The average number of students assigned to a vacancy is 3.
8. The average number of skills possessed by a student is 3
9. The number of different skills listed – 500
10. The average number of skills required for a vacancy is 2.

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:

Employer (E#, EName, EAddress, L#)

PK- E#, FK – L#

Location (L#, City)

PK – L#

Student (S#, Sname, SAddress, DateOfBirth)

PK- S#

Vacancy (V#, Description, S#, E#, L#)

PK- V#    FKs – S#, E#, L#

S# is used to keep track of students who have been offered jobs (filled).

Assign(S#, V#, Date_Assigned)

PK – S#, V#     FKs – S#, V#

Skill (Skill#, Description)

PK – Skill #

Possess (S#, Skill#)

PK – S#, Skill#  FKs – S#, Skill#

Require (V#, Skill#)

PK – V#, Skill#    FKs – V#, Skill#

• Populate tables with data (Enter at least five records to each table).

Include the INSERT SQL commands used to create tables in your report.

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

• Create index files to improve performance for appropriate tables. 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

• A suggestion of one possible de-normalisation (merging tables) to improve performance of the database in the ER Diagram provided. You must describe both positive and negative consequences of the merge. Attributes of the merged tables must be shown using standard relational notations indicating primary keys and the foreign keys in your report.

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

List of available vacancies

List of students

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

• 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.

Marking Schedule

	Expected Outcome	Marks Allocated
3	Introduction	5
4.1	Data volume Map	10
4.2	Create 8 tables of the database using Transact- SQL commands
	a.    Comments to create 8 tables	8
	b.    Primary key CONSTRAINTS included for each table	8
	c.     Foreign key CONSTRAINTS included (10 Required)	10
	d.    On Delete clause included for each foreign key CONSTRAINT.	10
4.3	Populate 8 tables using INSERT commands	8
4.4	Create Database Diagram for the database.	5
4.5	Create Index files (2 x 7)	14
4.6	De-normalisation
	a.    Suggestion	2
	b.    Table structure of the merged table with PK and  FK indicated	4
	c.     Positive impact of the change	2
	d.    Negative impact of the change.	2
4.7	Screen dumps of two reports
	a.    List of available vacancies	5
	b.    List of students	5
4.8	Data Dictionary ( 8 tables)	8

Total                                                                                      106