Database Systems: Design, Implementation, & Management
12th Edition
ISBN: 9781305627482
Author: Carlos Coronel, Steven Morris
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Concept explainers
Question
Chapter 3, Problem 18RQ
Program Plan Intro
Given Crow Foot Relational Diagram:
Primary Key:
A Primary Key in a database table is a field in the table that uniquely identifies every row or record present in the database table.
Example:
Students in Universities are assigned a unique registration number.
Therefore, in a STUDENT database table, the attribute “reg_no” acts as primary key.
Foreign Key:
Foreign Key is a column in a relational database table which provides a relation between two tables. It provides a cross reference between tables by pointing to primary key of another table.
Example:
In STUDENT database table, the attribute “reg_no” acts as primary key and in COURSE database table in which the student selects his or her course, the same “reg_no” acts as foreign key for the STUDENT table.
One to Many Relationship:
When one record in a database table is associated with more than one record in another table, the relationship between the two tables is referred as one to many relationship. It is also represented as1: M relationship. This is the opposite of many to one relationship.
Many to Many Relationship:
When more than one record in a database table is associated with more than one record in another table, the relationship between the two tables is referred as many to many relationship. This is represented as m: n relationship where m and n are two entities.
CROW FOOT ERD:
The Crow Foot ERD is an Entity Relationship Diagram which is used to represent the cardinalities present in the basic ER diagram. It is used to represent the relationships present between two tuples or tables present in the database.
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
Consider the sales transactions come from the following tables:1. Product (Product ID (PK), Product Name, Product Category (FK), Product Price)2. Employee (Employee ID (PK), Employee Name, Department Code (FK))3. Department (Department Code (PK), Department Location, Region) 4. Customer (Customer ID (PK), Customer Name, Customer Address, City Code (FK))5. City (City Code (PK), City Postcode, City State)6. Sales (Sales ID (PK), Date Sales, Product ID (FK), Employee ID (FK), Customer ID (FK), Quantity, Cost Sales)Draw the dimension modeling for producing the reporting.
Use the following diagram relation to perform the normalization.
You need to provide the 2NF and 3NF.
Consider the following schema:
Suppliers (sid, sname, city)
Parts (pid, pname, color)
Catalog (sid, pid, price)
Write relational algebra expressions for the following queries:
a. Find the sids of suppliers and names who are located in Riyadh.
b. Find the name of suppliers who are located in Riyadh and Jeddah.
c. Find the names of supplier who supply part name "fog light".
d. Find the sids of suppliers who supply every part.
Chapter 3 Solutions
Database Systems: Design, Implementation, & Management
Ch. 3 - What is the difference between a database and a...Ch. 3 - Prob. 2RQCh. 3 - Prob. 3RQCh. 3 - What are the requirements that two relations must...Ch. 3 - Which relational algebra operators can be applied...Ch. 3 - Explain why the data dictionary is sometimes...Ch. 3 - A database user manually notes that The file...Ch. 3 - Prob. 8RQCh. 3 - Prob. 9RQCh. 3 - Prob. 10RQ
Ch. 3 - Prob. 11RQCh. 3 - Prob. 12RQCh. 3 - Use Figure Q3.13 to answer Questions 1317. FIGURE...Ch. 3 - Create the table that results from applying a...Ch. 3 - Write the relational algebra formula to apply an...Ch. 3 - Create the table that results from applying an...Ch. 3 - Using the tables in Figure Q3.13, create the table...Ch. 3 - Prob. 18RQCh. 3 - Prob. 19RQCh. 3 - Prob. 20RQCh. 3 - Identify and describe the components of the table...Ch. 3 - Identify the primary keys. FIGURE Q3.22 THE...Ch. 3 - Identify the foreign keys. FIGURE Q3.22 THE...Ch. 3 - Create the ERM. FIGURE Q3.22 THE CH03_THEATER...Ch. 3 - Create the relational diagram to show the...Ch. 3 - Prob. 26RQCh. 3 - What would be the conceptual view of the INDEX...Ch. 3 - Prob. 1PCh. 3 - Prob. 2PCh. 3 - Do the tables exhibit referential integrity?...Ch. 3 - Describe the type(s) of relationship(s) between...Ch. 3 - Prob. 5PCh. 3 - Prob. 6PCh. 3 - Prob. 7PCh. 3 - Prob. 8PCh. 3 - Create the relational diagram to show the...Ch. 3 - Prob. 10PCh. 3 - Prob. 11PCh. 3 - Create the relational diagram to show the...Ch. 3 - Prob. 13PCh. 3 - Do the tables exhibit referential integrity?...Ch. 3 - Prob. 15PCh. 3 - Prob. 16PCh. 3 - For each table, identify the primary key and the...Ch. 3 - Prob. 18PCh. 3 - Do the tables exhibit referential integrity?...Ch. 3 - Identify the TRUCK tables candidate key(s). FIGURE...Ch. 3 - For each table, identify a superkey and a...Ch. 3 - Prob. 22PCh. 3 - Prob. 23PCh. 3 - Prob. 24PCh. 3 - Create the ERD. (Hint: Look at the table contents....Ch. 3 - Prob. 26PCh. 3 - Prob. 27PCh. 3 - Prob. 28PCh. 3 - Create the table that would result from applying...Ch. 3 - Create the table that would result from applying...Ch. 3 - Create the table that would result from applying a...
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- Given the following ERM, how would you convert this model into an ERM that displays only 1:M relationships?arrow_forwardQuestion 14 Consider the following schema and answer the following questions: Vendor (v ID: integer, v_Name: varchar, v_City: varchar) Product (p ID: integer, p_Name: varchar, p_colour: varchar) Catalog (v ID: integer, p ID: integer, Cost: decimal, Type: varchar) a. Write the following queries in relational algebra. i. Find the names of vendors who are from 'Suhar'. ii. Find the IDs of products which are supplied at cost greater than 1000. iii. Find IDs of vendors who supplies products with blue color.arrow_forwardQuestion: Draw a diagram showing the relationships for all the tables. The following tables need to be modified as shown (primary key attributes are bold, foreign key attributes are in italics): CID in Retail and Wholesale are defined as a primary key and a foreign key. CUSTOMER (CID, Name, Address) PHONE (PhoneNumber, Description, CID) RETAIL (CID, Type) WHOLESALE (CID, ContractID) CONTRACT (ContractID, Description) OLDCONTRACT (ContractID, Description) ORDER (OID, OrderDate, CID) PRODUCT (PID, Name, Price) DIVISION (DID, Name, Address) LINEITEM (OID, PID, Quantity, AmountCharged) PRODUCTION (DID, PID, Quantity, Rating) One-to-one Relationship If the foreign key will be the primary key or part of the primary key, choose the Identifying Relationship 1:1 icon. Otherwise, choose the Non-Identifying Relationship 1:1 icon.arrow_forward
- what is relational model representation of ER/EER model? A)student(studentno,name,firstname,midinitials,lastname,phone) Primary key: studentno B)student(studentno,name,phone) Primary key: studentno C)student(studentno,name,firstname,lastname,phone) Primary key: studentno D)student(studentno,fistname,lastname,phone) Primary key: studentnoarrow_forward1) The manager of an international company would like to know the quantity sold for each product and foreach store by day, week, month, and year. For each store, the manager would like to know its correspondingcity and country.You have to design a star schema to achieve the goal of this manager.2) If you think that some of your proposed dimension tables in Q1, are not normalized, you have to convertyour star schema into snowflake schema to normalize these dimension tables.arrow_forwardConsider the tables given below and write the relational Algebra for the following tasks Table Name: Products ProductID ProductName Unit Price SupplierlD 1 Chais 10 boxes x 20 bags 18 1 Chang 24 - 12 oz bottles 19 1 3. Aniseed Syrup 12 - 550 ml bottles 10 1 4 ChefAnton's Cajun Seasoning 48 - 6 oz jars 22 Table Name: Suppliers SupplierID SupplierName ContactName Address City 1 Exotic Liquid Charlotte 49 Gilbert St. Londona Сооper New Orleans Cajun Shelley Burke P.O. Box 78934 New Delights Orleans Grandma Kelly's Regina Murphy 707 Oxford Rd. Ann Arbor Homestead 4 Tokyo Traders Yoshi Nagase 9-8 Sekimai Tokyo Musashino-shi A. Write the Relational algebra for the following questions. i. Find all the information of products whose price is from 15 to 25 inclusively. ii. Find the lowest price of products for each supplier ID. iii. Find ID, name and address of suppliers whose city is Tokyo. B. Convert the following SQL statements to Relational Algebra expressions: i. Select * From Products…arrow_forward
- Suppose we have two tables. One table is Supplier with column supplieriD and ItemiD. The other table is Item, which has column ItemiD and Description. Which of the following returns itemiD for all items which are supplied by at least one supplier SELECT itemID FROM item WHERE EXISTS ( SELECT DISTINCT itemID FROM itemSupplier WHERE itemSupplier.itemID = item itemID) SELECT itemID FROM item WHERE itemID ( SELECT itemID FROM itemSupplier) IF EXISTS ( SELECT itemID FROM item Supplier) SELECT * FROM item SELECT itemID FROM item WHERE EXISTING ( SELECT DISTINCT itemID FROM itemSupplier) Given a table Students, to give each student a unique number based on credit hours he/she has obtained, which statement is correct? SELECT s.Name, s.CreditHours, ROW NUMBERO OVER (ORDER BY s.CreditHours DESC) AS 'Row Number From Students s SELECT s.Name, s.CreditHours, RANKO OVER (ORDER BY s.CreditHours DESC) AS Row Number From Studentss SELECT S.Name, s.CreditHours, DENSE RANKO OVER (ORDER BY s.CreditHours…arrow_forwardPlease let me know. thank you. please explain it well so I understand it.arrow_forwardThe below data is in first normal form (1NF). Normalise the data to second normal form (2NF), showing all steps with explanations. All steps and the final answer must be in dependency diagram format. Primary key: Player ID, Game ID Player Player ID Name 1 Bob 2 2 3 3 Sarah Sarah Liam Liam Player Nickname Rogue Game ID Game Name World of 2 Warcraft World of 2 Warcraft Warcraft 1 1 Wolverine 1 Wolverine 2 Professor 1 Professor 3 Genre ID III World of 2 Warcraft StarCraft 1 || Genre Description MMO MMO Strategy MMO Strategy Normalise the answer from Q.5.2 to third normal form (3NF), showing all steps with explanations. The final answer must be in dependency diagram format.arrow_forward
- 10. Determine the average salary of the employees who do not live in Greensboro. Rename the average salary as "Avg Sal". [Note: use ' or '¹!=']arrow_forwardGive employee Id, name, and date hired of the managers who have the highest experience (years employed) order by employee name. If there is more than one employee with the highest experience, show all. Q1.2 : Give all manager names and date hired for managers whose work experience is higher than the work experience of their employees. List results in managers' name order. Q1.3: List Material ID and Product Id in which the Material has the highest quantity required for this material. List results in order of Material ID. Q1.4: For products that have used materials, show the product ID, product description, material ID, and quantity required of materials whose quantity required is higher than the average quantity required for that material. Sort the results by product ID and then by material IDarrow_forwardThe below data is in first normal form (1NF). Normalise the data to second normal form (2NF), showing all steps with explanations. All steps and the final answer must be in dependency diagram format. Primary key: Player ID, Game ID Player Player ID Name 1 Bob 2 2 3 3 Sarah Sarah Liam Liam Player Nickname Rogue Game ID Game Genre Name ID World of 2 Warcraft World of 2 Warcraft Warcraft 1 Wolverine 1 Professor 1 Wolverine 2 Professor 1 3 ||| World of 2 Warcraft StarCraft 1 || Genre Description MMO MMO Strategy MMO Strategy Normalise the answer from to third normal form (3NF), showing all steps with explanations. The final answer must be in dependency diagram format.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning
Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning