Identify organisational information requirements

Assignment Help Computer Networking

Reference no: EM132121119

Data model development and implementation

Purpose of the assessment

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:
a. Understand the fundamental principles of the networking and data requirements of a network.
b. Identify organisational information requirements.
c. Model organisational information requirements using conceptual data modelling techniques.
d. Convert the conceptual data models into relational data models and verify their structural characteristics with normalisation techniques.
e. Implement and utilise a relational database using a database system.Assignment Description

SmartProjects Company is planning to design a Data model to hold information relating to their projects, Employees working on company Projects and their customers. Initial requirements analysis brings out the following User view requirements / business rules about what needs to be recorded. Assume that you are given a task to model the database by using these business rules.

Complete the information-level design of the database that satisfies the following constraints and user view requirements. In order to complete this information-level design you are required to answer questions 1(a) to 1(f).

User view 1 requirement : Each employeehas a unique ID number, First name, last name, and date of Birth, Address, user name and password.
An employee can work on only one project.

User view 2 requirements : Each customer has a unique Cust_number, first name, last name, address and Payment.
One customer can submit only one project at one time.

User view 3 requirements:Each project has a project numberthat uniquely identifies the project (PNum), title, StartDate, managerEmpNum and managerEmpName.
A Project can have one or more Employees associated with it.
One project may belongs to several departments.

User view 4 requirements: Each department has a unique ID number and name.
A Department can have Many Employees. An Employee can only work in One Department. A department controls number of projects.

Questions:

1.
a. Analyse the all user requirements given above, identify and list all entities described in each user requirement.

b. Add attributes to these entities and representthese entities (or Tables) and attributesas a collection of Tables and attributes/ database schema. You are required to arrange them as given in the example below.
Note:Select suitable primary key for each table and underlineit.
Eg. Employee (EmpNum,.......)
c. Outline all business rules that describe relationships between entities.
Note: You can make assumptions to create certain relationships, if it is not specifically mentioned in the given business rules.
Eg. One customer can submit only one project at one time.

d. Determine the functional dependences.
Eg. EmpNum →Fname, Lname, TaxFileNum,address ....

e. Then normalise these tables. Make the normalization to 3NF. State for every step in the normalization, which functional dependency causes it.

f. 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, thesemust be described.
Note: Youare required to use Visio or any other software tool to create the ER diagram.

2. Build this model using MS Access by creating these tables and Relationships. Populate these tables with appropriate data, at least 3 records in each table.
Include :
i. Relationship diagram created in Access. (Select database tools -→Relationships in the Access menu)

ii. Data sheet view and design view of your tables.

3. Write SQL queries for the following questions and execute themon the database you created in Question 2.
Include screen shots of the outputs and all SQL statements you used to answer following questions.

a. List details of allProjects started in year 2017

b. The management is interested in knowing names and email addressesof Customerswho paid more than $5000.

c. Explain how to formulate a query to print details of projects(Proj_Num, Project title, managerEmpNum) and employees(Employee ID, name) assigned to them.
Hint : You need to join Employee table and Project tables using a suitable query.

Attachment:- ASSIGNMENT.rar

Reference no: EM132121119

Questions Cloud

Type of data help healthcare organizations : how does this type of data help healthcare organizations (both clinical and management) with their operations and quality initiatives

What network device or devices can be used to address : In relation to Internet connectivity, both you and the chemists at Plains Research are concerned about security.

Create for three separate triangles : Create (construct instances) for three separate triangles (tr1, tr2, tr3) and assign positive values to leg1 and leg2 for each tringle.

Identify network threats using network security tools : BN203 Network Security Assessment - Identify Network Threats using Network Security Tools, Melbourne Institute of Technology, Australia

Identify organisational information requirements : MN405 - Data model development and implementation - Model organisational information requirements using conceptual data modelling techniques

Explain how you expect this phase to impact : Discuss any "ah-hah" or breakthrough moments you experienced during this phase of the project.

Why sometime there are two recursion trees shown : Why sometime there are 2 recursion trees shown. why sometimes the work per level decreases from level to level, and it is constant in each level.

Write a solution to the red or blue computation : In the first half step of an iteration, any red color can move right one cell if the cell to the right is unoccupied (white);

How much time would be required for a setup : Hammond Inc. has analyzed the setup time on its computer-controlled lathe. The setup requires changing the type of fixture that holds a part.

User Account

All Pages