Databases | SQL | Oracle | MS Access Answers

Given the relations R (A, B) and S (B, C) where values are all integers. Examine the undermentioned three relational algebra expressions

a. A, C(R⋈B=1S)

b. A(B = 1R)XC(B = 1S)

c. A, C (ARXB = 1S)

Two of the three expressions are equivalent (i.e., they produce the same answer on all databases), while one of them can produce a different answer

1. Which query can produce a contrasting answer? Give the simplest database instance you can think of where a different answer is produced
2. Why do we care that the expressions are equivalent?
3. How can we determine that the expressions are equivalent?

1.If we apply the cross-product operation to these two instances of STUDENTS & BOOKS, what would be the resulting relation?

STUDENTS

Student_ID Student_Name Qualification

401 Andile HCIT

402 Carol DIT

403 Amber BSc

404 Bright HCIT

BOOKS

Book_ID Title Copy-right

101 Database fundamentals 2017

102 C++ how to program 2018

103 Simply Visual Basic 2019

104 OOP for beginners 2020

1.1 SELECT and RETURN all students with qualification HCIT from relations STUDENTS X BOOKS

1.2 Write a relational algebra expression using the symbolic notation for the given question in (1.1) above.

Hillar Furniture Enterprise is a furniture store owned by Dr. Hillar Addo of AIT. The materials manager of this company maintains a list of suppliers for each of the materials items purchased by the company from outside vendors. The following table shows the essential data required for this application. Attribute Name Material_ID Material_ Name Unit_of_Measure Standard_Cost Vendor_ID Vendor_name Unit_price Terms_Code Terms Sample Value 3792 Hinges 3” locking Each GHC 5.00 V300 Apex Hardware GHC 4.75 1 COD a. Identify the functional dependencies for this data. You may assume the following: 6  Each material has one or more suppliers. Each supplier may have supply one or more items or may not supply any items.  The unit price for a material may vary from one vendor to another  The terms of a code for a supplier uniquely identifies the terms of a sale (e.g. code 2 means 10% net 30 days, etc.). b. Convert your data into a set of 3NF relations, identifying all the primary and foreign keys

QUESTION 2

Hillar Furniture Enterprise is a furniture store owned by Dr. Hillar Addo of AIT. The materials manager of this company maintains a list of suppliers for each of the materials items purchased by the company from outside vendors. The following table shows the essential data required for this application.

Hillar Furniture Company Purchasing Data is shown below:

Attribute Name Sample Value

Material_ID 3792

Material_ Name Hinges 3” locking

Unit_of_Measure Each

Standard_Cost GHC 5.00

Vendor_ID V300

Vendor_name Apex Hardware

Unit_price GHC 4.75

Terms_Code 1

Terms COD

a) Using the dependency diagram, identify the functional dependencies for this data. You may assume the following:

i. Each material has one or more suppliers. Each supplier may have supply

one or more items or may not supply many items.

ii. The unit price for a material may vary from one vendor to another

b) Convert your data into a set of 3NF relations, identifying all the primary and foreign keys

Draw interfaces for suppliers of bulling construction desktop application (with source code)

Question 4

a.

Attribute Name

Material_ID

Material_ Name

Unit_of_Measure

Standard_Cost

Vendor_ID

Vendor_name

Unit_price

Terms_Code

Terms

Sample Value

3792

Hinges 3” locking

Each

GHC 5.00

V300

Apex Hardware

GHC 4.75

1

COD

a. Identify the functional dependencies for this data. You may assume the

following:6

· Each material has one or more suppliers. Each supplier may have supply

one or more items or may not supply any items.

· The unit price for a material may vary from one vendor to another

· The terms of a code for a supplier uniquely identifies the terms of a sale

(e.g. code 2 means 10% net 30 days, etc.). The terms for a supplier are the

same for all material items ordered from that supplier.

b. Convert your data into a set of 3NF relations, identifying all the primary and

foreign keys 

Question 2

a. Describe the basic features of the relational data model and discuss their

importance to the end user and the designer.

b. Typically, a patient staying in a hospital receives medications that have been

ordered by a particular doctor. Because the patient often receives several

medications per day, there is a 1:M relationship between PATIENT and ORDER.

Similarly, each order can include several medications, creating a 1:M

relationship between ORDER and MEDICATION.

i. Identify the business rules for PATIENT, ORDER, and

MEDICATION.3

ii. Create a Crow's Foot ERD that depicts a relational database model

to capture these business rules.

c. Using an example in each case, explain the significant difference between

homonyms and synonyms and explain why should they be avoided in database

design?

Question 1

a. Although the database system yields considerable advantages over

previous data management approaches, database systems do impose

significant costs

b. Explain why the cost of ownership may be lower with a cloud database than

with a traditional, company database.

c. With clear examples, compare unstructured and structured and identify

which type of data is more prevalent in a typical business environment?