Question
In: Computer Science
Consider the following relation schema about project meetings: PMG(projID, title, type, manager, jobID, start-date, end-date, contractor,...
Consider the following relation schema about project meetings:
PMG(projID, title, type, manager, jobID, start-date, end-date,
contractor, contractNo)
Some notes on the semantics of attributes are as follows:
• Each project has a unique project ID (projID) and also has a
title, type and manager. Each manager has a specialty project
type.
• A project often contracts jobs to contractors with start-date and
end-date. Contracts are identified by contract numbers
(contractNo), but contract details are out of the scope of the
database.
FDs based on business rules are given as follows:
• projID → title, type, manager
• manager → type
• jobID → projID, start-date, end-date, contractor
• projID, title, jobID → contractNo
• contractNo → jobID, contractor, start-date, end-date
• jobID → contractNo
Answer questions below:
2.1. (3 points) The given FDs have redundancies. Give the
minimal basis for the given FDs.
2.2. (3 points) The PMG relation is not in BCNF or 3NF.
Explain why. Your explanation must be based on the
functional dependencies in Question 2.1.
2.3. (3 points) Decompose the PMG relation into relations in BCNF
or 3NF. Your decomposition must keep all functional dependencies
and must be lossless. For each resultant relation, discuss if it is
in BCNF or 3NF and indicate the primary key (underline) and any
foreign keys (*). Note that relations must be written in the form
as shown in the examples below:
Student(sno, name, address)
Course(cno, title)
Take(sno*, cno*, grade)
Note: Please mention the question numbers clearly before answering the question. (e.g. 2.1 - (then the answer)
Solutions
venereology answered 3 hours agoRelated Solutions
Consider the following universal relation THE following database schema is in 4NF. What can you infer...
Consider the following universal relation
THE following database schema is in 4NF. What can you infer
about multi-valued dependencies?
A
C
D
A
B
C
E
E
F
A-It does not have multi-valued dependencies.
B-The multi-valued dependency A ->-> C does not hold. If
the multi-valued dependency A ->-> C held, the database would
not be in 4NF.
C-The multi-valued dependency A ->-> B does not hold. If
the multi-valued dependency A ->-> B held, the database would
not be...
what and how usually project manager, developer and contractor discuss about a project's drawing. what the...
what and how usually project manager, developer and contractor
discuss about a project's drawing. what the common problems in the
drawing and how they solve it.
Consider the following database schema: Product(maker, model, type) PC(model, speed
Consider the following database schema:
Product(maker, model, type)
PC(model, speed, ram, hd, rd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)
Give SQL statement for each of the following:
(Grouping and Aggregation) Write the following
queries in SQL:
Find the average speed of laptops costing over $2000.
Find the average price of PC’s and laptops made by manufacturer
“D”.
Find, for each manufacturer, the average screen size of its
laptops.
Find the manufacturers...
Consider the following database schema: Product(maker, model, type) PC(model, speed
Consider the following database schema:
Product(maker, model, type)
PC(model, speed, ram, hd, rd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)
Give SQL statement for each of the following:
(Subqueries, Join operations) Write the
following queries in SQL:
Find the makers of PC’s with a speed of at least 1200.
Find the printers with the highest price.
Find the laptops whose speed is lower than that of any PC.
Find the model number...
SQL query exercises: Consider the following database schema: Product(maker, model, type) &
SQL query exercises:
Consider the following database schema:
Product(maker, model, type)
PC(model, speed, ram, hd, rd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)
Consider the Drivers-Cars-Reserves DB for a small rental car
company:
Drivers(id, name, rating, age)
Cars(vin, maker, model, year, color)
Reserves(did, vin, date)
Give SQL statement each of the following operations:
Find the colors of cars reserved by Smith.
Find all id’s of drivers who have a...
Consider the following schema: Publisher (name, phone, city), PK: name. Book (ISBN, title, year, published_by, previous_edition,...
Consider the following schema:
Publisher (name, phone, city), PK:
name.
Book (ISBN, title, year, published_by,
previous_edition, price), PK: ISBN, FK: published_by refs
Publisher, previous_edition refs Book.
Author (SSN, first_name, last_name,
address, income), PK: SSN.
Write (aSSN, bISBN), PK: (aSSN,
bISBN), FK: aSSN refs Author, bISBN refs Book.
Editor (SSN, first_name, last_name,
address, salary, works_for, book_count), PK: SSN, FK: works_for
refs Publisher.
Edit (eSSN, bISBN), PK: (eSSN, bISBN),
FK: eSSN refs Editor, bISBN refs Book.
Author_Editor (aeSSN, hours), PK:
aeSSN, FK:...
Consider the following schema: Publisher (name, phone, city), PK: name. Book (ISBN, title, year, published_by, previous_edition,...
Consider the following schema:
Publisher (name, phone, city), PK:
name.
Book (ISBN, title, year, published_by,
previous_edition, price), PK: ISBN, FK: published_by refs
Publisher, previous_edition refs Book.
Author (SSN, first_name, last_name,
address, income), PK: SSN.
Write (aSSN, bISBN), PK: (aSSN,
bISBN), FK: aSSN refs Author, bISBN refs Book.
Editor (SSN, first_name, last_name,
address, salary, works_for, book_count), PK: SSN, FK: works_for
refs Publisher.
Edit (eSSN, bISBN), PK: (eSSN, bISBN),
FK: eSSN refs Editor, bISBN refs Book.
Author_Editor (aeSSN, hours), PK:
aeSSN, FK:...
Consider the following relational schema about a University (the primary keys are underlined and foreign keys...
Consider the following relational schema about a University (the
primary keys are underlined and foreign keys are italic)
STUDENT(StudentID, name, major, year, age) CLASS(ClassName,
meetsAt, room, LecturerID) ENROLLED(StudentID, ClassName, mark)
LECTURER(LecturerID, name, DepartmentID) DEPARTMENT(DepartmentID,
name) Write the SQL statements for the following query:
B1. Find the age of the oldest student.
B2. Find the ID’s of lecturers whose name begins with “K” \
B3. Find the age of the youngest student who is enrolled in
Mechatronics.
B4. Find the age...
As you consider this question about a project team for a new smart phone, what type...
As you consider this question about a project team for a new
smart phone, what type of people do you need from inside the
organization – and do you need anyone from outside the organization
in beginning? If not, perhaps at a later time?
Consider the following relation AIRLINE TABLE Flight Date Flight ID Origin Destination Captain ID Captain Name...
Consider the following relation
AIRLINE TABLE
Flight
Date
Flight
ID
Origin
Destination
Captain
ID
Captain
Name
Number of Passengers on the Flight
April 10
111
Chicago
L.A.
111
Tracy
110
April 10
222
L.A.
Phoenix
222
Matt
105
April 12
111
Chicago
L.A.
444
Tim
98
April 12
333
L.A.
N.Y.
111
Tracy
75
April 12
444
L.A.
N.Y.
333
Pat
110
April 12
555
L.A.
Dallas
444
Tim
111
April 12
222
L.A.
Phoenix
555
Pat
107
Some of...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
- explain the relationship between the SEC and the various private sector standard setting bodies that have,...
- You have been asked to prepare a December cash budget for Ashton Company, a distributor of...
- Holly bought a 7-year bond, with a 3% coupon paid semi annually. It was priced to...
- METAR Abbreviations METAR (Meteorological Aviation Report) is a format for reporting weather information, either for pilots...
- The data below are the heights of fathers and sons (inches). There are 8 rows in...
- Without quantization procedure how much of actual information is lost during analog to digital conversion process??...
- 11. Suppose that a 32M × 16 memory built using 512K × 8 RAM chips and...
ADVERTISEMENT