Step 1. Draw an ER diagram for the story below. Use the ERD tool only. Draw Relations in Oracle notation. Please read the story carefully. Use the mentioned Entities ONLY. The company sells various Products to Customers. The Sales Agent books an Order. A new order is created for each Product. While booking an Order, Agent records Order Date, Product, Customer, Product Quantity, and Sales Agent. Product has Description and UnitPrice. Each Agent reports to a Manager. A Manger has a Name and Commission Percent.

Step 1. Draw an ER diagram for the story below. Use the ERD tool only. Draw Relations in Oracle notation. Please read the story carefully. Use the mentioned Entities ONLY. The company sells various Products to Customers. The Sales Agent books an Order. A new order is created for each Product. While booking an Order, Agent records Order Date, Product, Customer, Product Quantity, and Sales Agent. Product has Description and UnitPrice. Each Agent reports to a Manager. A Manger has a Name and Commission Percent.

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter2: Data Models

Section: Chapter Questions

Problem 11P

See similar textbooks

Similar questions

What type of relationship can’t be stored in a relational database? Why?
Write the Relation Model, using SQL, for the gig Relationship Set shown in this ER Model: The Band and Venue Entity Sets both have the conflicting name as their Primary Keys. Only the gig relation needs to be defined for this question.
produce a list of relations indicating the primary key, columns and foreign keys. Consider the following attributes for tables in a relational model designed to keep track of information for a moving company that moves residential customers, usually from one home or apartment to another:customerID, customerName, customerCurrentAddress, customerCurrentPhone, customerNewAddress, customerNewPhone, pickupLocation, dropOffLocation, dateOfMove, startingTime, estimatedWeight, estimatedCost, truck#Assigned, driverName, driverLicNo, actualCost, amountOfDamages, truckCapacity, costOfTolls, tax, finalAmount, invoiceNumber, amountPaid, datePaid, checkNumber, amountDueAssume:- Although in most cases the pickupLocation is the customer’s old address and the dropOffLocation is the new address, there are exceptions, such as when furniture is moved to or from storage.-An estimate is provided before the move using a pre-printed invoice containing a unique invoice number. The actual cost is recorded on…
Consider two relations Order and Product with the schema Order (Oid, Odate, Pid, qty) and Product (Pid, manufacturer_id, unit_price). i) Can the Oid attribute in the relation Order be a candidate key of the relation? Justify your answer. ii) Can the Pid attribute in the relation Product be a candidate key of the relation? Justify your answer. iii) Find the candidate key for the relation Product. iv) Using the primary keys identified for the above relations write a SQL query that retrieves all tuples of the form (Oid, Odate, Pid, unit_price).
For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None in the space provided. Do the tables exhibit entity integrity and referential integrity? Answer yes or no and then explain your answer. In the case of Referential Integrity write NA (Not Applicable) if the table does not have a foreign key. iii. Describe the type(s) of relationship(s) between STORE and REGION Create the ERD to show the relationship between STORE and REGION.6 Describe the type(s) of relationship(s) between EMPLOYEE and STORE. (Hint: Each store employs many employees, one of whom manages the store.) Draw the ERD to show the relationships among EMPLOYEE, STORE and REGION.
The following ERD refers to an borrow system of a library. Member can borrow different books, which were written by different authors and published by different publishers. A writer can be contracted to one or more publishers. Each member can have different borrows with different books to different expiring dates.a)Give two Attributes for each entity. Draw them directly into the ER-diagram. b)Give a primary key for each entity. Draw it directly into the ER-diagram. c)Create the SQL statement for implementing the table for one of the entities of the up standing ERD
Using your knowledge of the college environment, determine the functional dependencies that exist in the following table. After determining the functional dependencies, convert this table to an equivalent collection of tables that are in third normal form. Do not create a diagram of the tables. Instead use the symbolism like that found in the textbook when showing functional dependencies. (StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName, DeptNum, DeptName, CourseNum, Description, Term, Grade)
COM. Q. CUSTOMER (CID, NAME, STREET, CITY, STATE, AGE) SALE (PID, CID, DATE, COST) PART (PID, PNAME, COLOR) The meaning of these relations is straightforward; for example, SALE has one tuple for every part sold to a customer. Primary key attributes are underlined. For instance, PID and CID together form the primary key of relation SALE. SALE.PID is a foreign key that refers to PART.PID, while SALE.CID is a foreign key that refers to CUSTOMER.CID. Write SQL statements for the following retrieval and update operations. Retrieve the names of all the customers from the city of "Trenton' who bought a part at a cost less than $50. No duplicates should appear in the answer. Retrieve the names of red parts bought by a customer who is younger than 40 and from a customer who is older than 40. Delete all parts that are never bought by a customer from "Trenton" (this is a data change statement). Retrieve the names of the customers who never bought a green part. Retrieve the names of the…
write the sql queries for the following er diagram: Create all the tables and relationships (including all primary keys, foreign keys, attributes and appropriate data types) shown in the ER diagram above.and Insert 3 records into each table. In the Item table, consider the first record as your favorite dress, second record as a gift that you have recently bought to someone, and the third record as your most favorite accessory that you are currently using; then insert the appropriate data. Similarly, insert some valid data into the other three tables. and Write an SQL statement to update the third row of Item table: Item_Name as ‘Hat’, Brand as ‘Puma’, and Type as ‘Ferrari’. and Write an SQL statement to display the Branch_ID, Location_ID, Item_ID, Units_Sold and Dollars_Sold of each customer. Results should be in a descending order of Item_ID.and also Write an SQL statement that displays the number of items, the sum of units_Sold and the sum of dollars_sold in each location. (Hint: Use…
Using SQL data definition language, write the SQL statements to create each of the following relations. i. student(student id, name, dept id, total credits)ii. department(dept id, building, budget)iii. lecture_hall(hall id, campus, capacity)iv. course(course id, title, dept id, credits) v. takes(student id, course id, semester, year, grade)
Determine the relationships among the four relations in Figure 7-17. List primary keys for each relation and any foreign keys necessary to establish the relationships and maintain referential integrity. Pay particular attention to the data contained in TUTOR REPORTS when you set up its primary key.
Using the ER diagram for Supplier and Part entities provided (attached here), convert the ERD logical design into a physical database using Microsoft Access. Make sure that you have to create both tables, then use "Database Tools"/"Relationship" to set a specific relationship type between two entities. Once you created both tables, type in at least 5 sample records per entity table ( special care needed when you enter sample data in FK field). Perform a Query to obtain "Supplier_ID", "Supplier_Name", "Part_Num", "Part_Name" data from both entity tables. Also create a form and report using any entity

SEE MORE QUESTIONS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

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

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

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

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS