Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

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

1 answer below »

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

Answered Same Day May 27, 2020

Solution

Ankit answered on May 31 2020
123 Votes
a) Analyse the all user requirements, identify and list all entities described in each user requirement.
    User requirement
    entities
    User view 1 requirement
    Student
    User view 2 requirement
    Jobs
    User view 3 requirement
    Student_jobapply
    User view 4 requirement
    Interviews
    User view 5 requirement
    Interviewer, Company
) Add attributes to these entities and represent these entities (or Tables) and attributes as a collection of Tables and attributes. You are required to a
ange them as given in the example below. 
Answer:
Student (student_id, student_fname, student_lname, student_licenseno, expected_graduation_date)
Jobs (job_id, Job_title, job_requirement, job_description, job_base_salary, job_date, job_application_deadline)
Company (company_id, company_identifier, company_name, company_contactno, company_fasno)
Interviews(interview_id, interview_date, interview_conference_room, employee_id, student_id, interview_result)
Interviewer (employee_id, employee_phoneno,...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here