Fundamentals Of Relational Database Management System

[عيونكـ دنيتي]

السلام عليكم
انا عندي هالماده
Fundamentals of Relational Database Management System
وابي حل هالسينايووو task 1 & 2& 3

Scenario:

Consider the following scenario modelling project, manager, specialist engineer, quality supervisor, and the like at an organization.

A project manager is responsible for a project. He is uniquely identified by a id. A project manager has a name, specialization, experience and details of projects. A project manager can supervise one or more projects. Each project has a unique id, a project name, duration and total cost. A project can have only one project manager. Each project has a quality supervisor. A quality supervisor has unique id, name, designation and certification details. A quality supervisor may or may not be assigned to any project or can be assigned to more than one project. A group of specialist engineers work for a project. Each specialist engineer has a unique id, name, specialization and experience. A specialist engineer can work for more than one project at a time. However, he should work for at least one project. A specialist engineer reports to one or more

project managers. A project manager may not be having any specialist engineers reporting to him. Each business group may have many projects or may not be having any projects. A business group is identified by a name. A business group has focus area and description. One or more project managers belong to a business group. Each business group should have at least one project manager. A business head handles one more business groups. However, a business group has exactly one business head. A business group head has a name, position name and address.

Task 1: Construct the Entity Relationship Diagram (ERD) for the above given scenario. Identify the different entities, the attributes of each entity including the Primary key, not null, relationship between the entities and the integrity constraints.


Task 2: Normalize the below given resource allocation information into First Normal Form, Second Normal Form and Third Normal Form. Indicate primary key and foreign key.


A resource allocation sheet prepared for every project. Resource allocation contains the details of the engineer along with the number of days that an engineer works for the project. A project is assigned a unique identity number and each project will be carried out for a customer.


Resource Allocation document ID : 2244
Project ID: 1122
Customer ID: 255
Project Name: MP3 Codec
Customer Name: Oman Audio Solutions
Employee ID Employee Name Designation Number of Days
1021 Ali Project Manager 40
2109 Basher Analyst 60
1208 Nada Programmer 45





Task 3:

Write the SQL statements of the following:
i. Create the tables that you identified in Task 2. Include different identity constraints (e.g. primary key, foreign key, not null, integrity constraints etc.).
ii. Insert two records each to any table you created in Task (3 i)
iii. Write a SQL query to display the employee id of engineers whose name has last character/alphabet ‘a’ and project name is ‘Mp3 Codec’ (Refer Task 2

).

تم تعديل 15 أبريل 2010 بواسطة عيونكـ دنيتي

[عيونكـ دنيتي]

سااااااااااااااااااااااااااااااااااااااااااااااااااعدوني بليــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــز