![Database Systems: Design, Implementation, & Management](https://www.bartleby.com/isbn_cover_images/9781285196145/9781285196145_largeCoverImage.gif)
Concept explainers
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.
![Check Mark](/static/check-mark.png)
Trending nowThis is a popular solution!
![Blurred answer](/static/blurred-answer.jpg)
Chapter 3 Solutions
Database Systems: Design, Implementation, & Management
- 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
- PLEASE MAKE THE CORRECT ERD AND THEN CONVERT IT INTO RELATIONAL MODEL BY KEEPING ALL RULES OF IT IN MIND A hardware store sells several home workshop products to the public (such as power saws and sanders). Each product has several different manufacturers who manufacture it, and prices are different for products made by different manufacturers. Product has product no, product name, product details while manufacturers are assigned unique manufacturer number, name, address and phone numbers. Each time one or more products are sold to a customer, an invoice is created which lists the date, items purchased and their prices, and then the total purchase and tax amounts. Consider the above scenario you are required to create ERD for the above scenario first then covert the ERD into Relational Model.arrow_forwardSuppose 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_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
- Give 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_forwardIdentify the class of entity represented by each of the lettered symbols in the REA model.arrow_forward
- 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 LearningNp 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
![Text book image](https://www.bartleby.com/isbn_cover_images/9781285196145/9781285196145_smallCoverImage.gif)
![Text book image](https://www.bartleby.com/isbn_cover_images/9781305251038/9781305251038_smallCoverImage.gif)
![Text book image](https://www.bartleby.com/isbn_cover_images/9781305627482/9781305627482_smallCoverImage.gif)