The School of Information Technology & Engineering (SITE) would like to help its Master of Networking students with placement in various companies and firms. To facilitate this, they need to design a database with the primary purpose of scheduling interviews and facilitating searches by students and companies that are looking for candidates. MIT’s IT Manager provided following business rules to the software developer:
Complete the information-level design for the new database “MIT training” that satisfies the constraints and user view requirements given below. In order to complete the information-level design you are required to answer questions given below 1(a) – (e)
User view 1 requirement: The database should have information about students, job openings, companies, interviewers and interviews. Student’s first name, last name, student id number, driver’s license number and expected graduation date must be recorded.
User view 2 requirements: Companies post job openings that students can apply for. For each job posting, the date, application deadline of the posting, title of the position, base salary, minimum requirements, and description are recorded.
User view 3 requirements: One student can apply for many open positions (Job postings) in different companies, and the date and time of each application is recorded. Students should also be able to see the status of their application (whether there will be a follow-up interview and whether they were turned down for the position).
User view 4 requirements: Interviews can be facilitated by the University and interviews are conducted by the respective companies. It is required to keep track of date and time of the interview, conference room location, which employees interviewed which students, as well as the result of the interview. A company from the Business School reserves MIT conference rooms and the company needs to specify the resources needed (e.g. computer, projector, etc.).
User view 5 requirements: An interviewer is an employee of a company and he/she has an employee id, telephone number and position title.
User view 5 requirements: A company has an identifier, name, main contact number and main fax number.
a. Analyse the all user requirements, identify and list all entities described in each user requirement.
(5 Marks)
b. Add attributes to these entities and represent these entities (or Tables) and attributes as a collection of Tables and attributes. You are required to arrange them as given in the example below.
NB: Select a suitable primary key for each table and underline it.
Eg. Student (StudentID, Fname,…….
(10 Marks)
c. Outline all relationships between entities.
Eg. : One students can apply for many open positions (Job postings) in all companies - One-to-many,
(5 Marks)
d. Determine the functional dependences.
Eg. StudentID àFname, address, ......
(10 Marks)
e. Then normalise these tables. Make the normalization to 3NF. Show every step in the process.
(10 Marks)
2. Represent the structure of your database visually by using the entity-relationship (E-R) diagram. If you make any assumptions about data that doesn’t appear in the problem, these must be clearly described.
You need to use Visio or any other software tool to create the ER diagram.
(15 Marks)
3. Build this model using MS Access/ MS SQL Server by creating these tables and Relationships. Populate these tables with appropriate data, include at least 3 records in each table.
(10 Marks)
4. Give one example of a type report that can be obtained from this “MIT training” database.
(5 Marks)
5.
Marking criteria:
Example of marking criteria is shown in the following table. Marks are allocated as follows:
| Section to be included in the report | Description of the section | Marks |
| 1. | | |
| a. Identify all entities | Identify entities, and all user requirements | 5 |
| b. User Views as Tables | Represent the User Views as a collection of Tables | 10 |
| c. Relationships | Determine the relationships between entities | 5 |
| d. Functional dependences. | Determine Functional dependences. | 10 |
| e. Normalise tables | Analyse above tables and normalise | 10 |
| 2. | | |
| ER Diagram | Represent the structure of your database visually by using the ER diagram. Evaluate your proposed solutions | 15 |
| 3. Model building | Model building on MS SQL Server | 10 |
| 4. Report Generation | Example report | 5 |
| | Total marks or the Report | 70 |
| Assignment 2 Quiz marks | Marks for Assignment 2 Quiz | 30 |
| | TOTAL | 100 |