Intro to Database - Complete Lab Manual Solution, Lab Reports of Database Management Systems (DBMS)

Download Intro to Database - Complete Lab Manual Solution and more Lab Reports Database Management Systems (DBMS) in PDF only on Docsity!

Lab 1

Introduction to MS Visio

CANDIDATE

History

HAS

Lab # 01

Task: During peak periods, Temporary Employment Corporation (TEC) places temporary

workers in companies. TEC’s manager gives you the following description of the business:  TEC has a file of candidates who are willing to work.  If the candidate has worked before, that candidate has a specific job history. (Naturally, no job history exists if the candidate has never worked.) Each time the candidate works, one additional job history record is created.

Lab Tasks: Identify all possible entities, their attributes and draw these entities with

attributes in MS Visio. Lab Task 1: The Company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department. A department may have several locations.  A department controls a number of projects, each of which has a unique name, a unique number, and a single location.  The database will store each employee’s name, CNIC, address, salary, sex (gender), and birth date. An employee is assigned to one department, but may work on several projects, which are not necessarily controlled by the same department. It is required to keep track of the current number of hours per week that an employee works on each project, as well as the direct supervisor of each

Answer:

Entity Name Entity Attributes

EMPLOYEE Name, Gender, Address Date birth, Salary, address

DEPARTMENT Name, Number, location

PROJECT Name, Number, location

Lab # 02 Task: During peak periods, Temporary Employment Corporation (TEC) places

temporary workers in companies. TEC’s manager gives you the following description of the business:  TEC has a file of candidates who are willing to work.  If the candidate has worked before, that candidate has a specific job history. (Naturally, no job history exists if the candidate has never worked.) Each time the candidate works, one additional job history record is created.  Each candidate has earned several qualifications. Each qualification may be earned by more than one candidate. (For example, it is possible for more than one candidate to have earned a BBA degree or a Microsoft Network Certification. And clearly, a candidate may have earned both a BBA and a Microsoft Network Certification.)  TEC also has a list of companies that request temporaries.  Each time a company requests a temporary employee, TEC makes an entry in the Openings folder. That folder contains an opening number, a company name, required qualifications, a starting date, an anticipated ending date, and hourly pay.  Each opening requires only one specific or main qualification.  When a candidate matches the qualification, the job is assigned and an entry is made in the Placement Record folder. That folder contains an opening number, a candidate number, the total hours worked, etc. In addition, an entry is made in the job history for the candidate.  An opening can be filed by many candidates, and a candidate can fill many openings. Solution: TEC’s management wants to keep track of the following entities:

1. COMPANY
2. OPENING
3. QUALIFICATION
4. CANDIDATE
5. JOB_HISTORY
6. PLACEMENT
7. COURSE
8. SESSION

Lab 3

Experiment # 03: Notations for Relationships

Lab # 03 Tasks: Draw Entity Relationship Diagram for the following requirements

Lab Task 1: The Company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department. A department may have several locations.  A department controls a number of projects, each of which has a unique name, a unique number, and a single location.  The database will store each employee’s name, CNIC, address, salary, sex (gender), and birth date. An employee is assigned to one department, but may work on several projects, which are not necessarily controlled by the same department. It is required to keep track of the current number of hours per week that an employee works on each project, as well as the direct supervisor of each

Lab # 04

Enhanced Entity Relationship

Diagram (EERD)

Lab # 04 Tasks: Draw Enhanced Entity Relationship Diagram for the following requirements.

Lab Task 1: Consider an ONLINE_AUCTION database system in which members (buyers and sellers) participate in the sale of items. The data requirements for this system are summarized as follows:  The online site has members, each of whom is identified by a unique member number and is described by an e-mail address, name, password, home address, and phone number.  A member may be a buyer or a seller. A buyer has a shipping address recorded in the database. A seller has a bank account number and routing number recorded in the database.  Items are placed by a seller for sale and are identified by a unique item number assigned by the system. Items are also described by an item title, a description, starting bid price, bidding increment, the start date of the auction, and the end date of the auction.  Items are also categorized based on a fixed classification hierarchy (for example, a modem may be classified as COMPUTER → HARDWARE → MODEM).  Buyers make bids for items they are interested in. Bid price and time of bid are recorded. The bidder at the end of the auction with the highest bid price is declared the winner, and a transaction between buyer and seller may then proceed.  The buyer and seller may record feedback regarding their completed transactions. Feedback contains a rating of the other party participating in the transaction (1–10) and a comment.

Solution on next page

Lab Task 2:  The database keeps track of three types of persons: employees, alumni and students. A person can belong to one, two, or all three of these types. Each person has a name, SSN, sex, address, and birth date.  Every employee has a salary, and there are three types of employees: faculty, staff and student assistants. Each employee belongs to exactly one of these types. For each alumnus, a record of the degree or degrees that he or she earned at the university is kept, including the name of the degree, the year granted, and the major department. Each student has a major department.  Each faculty has a rank, whereas each staff member has a staff position. Student assistants are classified further as either research assistants or teaching assistants, and the percent of time that they work is recorded in the database. Research assistants have their research project stored, whereas teaching assistants have the current course they work on.  Students are further classified as either graduate or undergraduate, with the specific attributes degree program (M.S., Ph.D., M.B.A., and so on) for graduate students and class (freshman, sophomore, and so on) for undergraduates.

Lab ## 05

Conversion of ERD into relational

schema

Lab Task 2:

Solution on next page

Div-code Div_Name Emp-Num

DIVISION

DEPARTMENT

Emp-Num Emp_Fname Emp_Lname Emp_Initial Emp_Extension Emp_Email Dept-code

EMPLOYEE

Assign-Num Assign_Date Assign_Hours Emp-Num Proj-code

ASSIGNMENT

PROJECT

Dept-code Dept_Name Div-code Proj-code Proj_Name

Lab # 06 Tasks: Transform the following EERD into relational schema. Lab Task 1:

EMPLOYEE:

Emp-id Name Address

SECRETRY:

Sectr-id Typing_speed

HOURLY_EMPLOYEE

TECHNICIAN:

SALARIED_EMPLOYEE

MANAGER:

Manage-id Manag_start_date

ENGINEERING MANAGER:

Emp_id Hourly_rate Tech-id T_Grade Emp_id Salary Manage-id Experience

Lab Task 2:

Solution: