Reference no: EM132373307

Assessment - Data model development and implementation

Purpose of the assessment (with ULO Mapping) The purpose of this assignment is to develop data models and map Database System into a standard development environment to gain understanding of data model development. Then implement the data model using a commercially available database management system development tool.

On completion of this assignment students will be able to:
b. Make an informed and critical assessment of database management systems.
c. Develop data models and implement DB systems.
d. Analyse business decisions related to DB information systems.
e. Demonstrate skills in building a database application using a commercially available database management system development tool.

Assignment Description

1. Database Design

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 IT Manager provided following business rules to the software developer:

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, 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 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 6 requirements: A company has an identifier, name, main contact number and main fax number.

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)

a. Identify and list entities described in all user requirements given above.
b. Add attributes to these entities and represent them as a collection of tables and attributes as below.
Eg. Student (StudentID, Fname,.......

NB: Select suitable primary key for each table and underline them.

c. Outline at least 2 relationships between those entities.

Eg. : One students can apply for many open positions (Job postings) in companies - One-to-many,

d. Determine the functional dependences.
Eg. StudentID → Fname, address, ......

e. Normalise these tables. Make the normalization to 3NF. State for every step in the process.

2. Represent the structure of your database visually by using the entity-relationship (E-R) diagram. If you make any assumptions about data that are not explicitly given in the problem, these must be described.

NB: You need to use Visio, draw.io [1] or Lucidchart [2] or any other software tool, to create the ER diagram.

3. Model Building
Build this model using MS Access/SQLite by creating these tables and Relationships. Populate these tables with appropriate data, at least 2 records in each table. Include following into your MS Word document.

I. If you are using MS Access include following screen shots
· Relationship diagram created in Access. (Select database tools Relationships in the Access menu)
· Data sheet view and design view of your tables. Eg.

II. If you are using SQLite
• Screen shots of CREATE TABLE command and INSERT INTO commands. Eg.

4. Report Generation
a. Write a query to generate one example for useful information that can be obtained from this "MIT training" database.
b. Execute the query on the database and attach the screenshot of your output.