1) Create an Entity-Relationship (E-R) model for a new company that recently started a business where they buy highly discounted items in bulk from different companies who are going out of business or need to get rid of additional items to save warehouse space. This new company has retail stores in big cities across the country where they sell the items to individuals. They also do business as wholesalers where they sell items in bulk to local retailers in small cities. Therefore, they have two types of customers: individuals and small retailers. The company has four departments: purchase, warehouse, sales (retail and wholesale) and accounting. They employ five different types of employees: managers, accounting staffs, warehouse staffs, retail associates, and purchase agents. Each staff, agent and associate works under a manager of the respective department. Purchase agents hunt for discount items, and they get a commission based on the items they are successfully able to buy. You have freedom to make necessary assumptions about relations and attributes to create a realistic model. However, you must incorporate at least one composite and one multivalued attribute in the model. 2) Draw your model in LucidChart or any other similar software of your choice. However, you must follow the notations and conventions that have been primarily demonstrated in the textbook and the lecture slides. You will not receive any marks if you fail to follow this. In the design, identify all primary keys and mapping cardinalities correctly. 3) Convert all entity and relationship sets into relation schemas. You do not have to draw the schema. Just write the schema names followed by attribute names. E.g., schema1(att1,att2,att3). You do not need to identify foreign keys. 4) If any of the relation schema representing a relationship set is redundant, remove that. Mention your justifications. 5) After removing redundancies, for each relation schema list a reasonable set of functional dependencies that should exist.
1) Create an Entity-Relationship (E-R) model for a new company that recently started a business where they buy highly discounted items in bulk from different companies who are going out of business or need to get rid of additional items to save warehouse space. This new company has retail stores in big cities across the country where they sell the items to individuals. They also do business as wholesalers where they sell items in bulk to local retailers in small cities. Therefore, they have two types of customers: individuals and small retailers. The company has four departments: purchase, warehouse, sales (retail and wholesale) and accounting. They employ five different types of employees: managers, accounting staffs, warehouse staffs, retail associates, and purchase agents. Each staff, agent and associate works under a manager of the respective department. Purchase agents hunt for discount items, and they get a commission based on the items they are successfully able to buy. You have freedom to make necessary assumptions about relations and attributes to create a realistic model. However, you must incorporate at least one composite and one multivalued attribute in the model. 2) Draw your model in LucidChart or any other similar software of your choice. However, you must follow the notations and conventions that have been primarily demonstrated in the textbook and the lecture slides. You will not receive any marks if you fail to follow this. In the design, identify all primary keys and mapping cardinalities correctly. 3) Convert all entity and relationship sets into relation schemas. You do not have to draw the schema. Just write the schema names followed by attribute names. E.g., schema1(att1,att2,att3). You do not need to identify foreign keys. 4) If any of the relation schema representing a relationship set is redundant, remove that. Mention your justifications. 5) After removing redundancies, for each relation schema list a reasonable set of functional dependencies that should exist.
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
Related questions
Question
Transcribed Image Text:1) Create an Entity-Relationship (E-R) model for a new company that recently started a business
where they buy highly discounted items in bulk from different companies who are going out of
business or need to get rid of additional items to save warehouse space. This new company has
retail stores in big cities across the country where they sell the items to individuals. They also do
business as wholesalers where they sell items in bulk to local retailers in small cities. Therefore,
they have two types of customers: individuals and small retailers. The company has four
departments: purchase, warehouse, sales (retail and wholesale) and accounting. They employ five
different types of employees: managers, accounting staffs, warehouse staffs, retail associates,
and purchase agents. Each staff, agent and associate works under a manager of the respective
department. Purchase agents hunt for discount items, and they get a commission based on the
items they are successfully able to buy. You have freedom to make necessary assumptions about
relations and attributes to create a realistic model. However, you must incorporate at least one
composite and one multivalued attribute in the model.
2) Draw your model in LucidChart or any other similar software of your choice. However, you must
follow the notations and conventions that have been primarily demonstrated in the textbook
and the lecture slides. You will not receive any marks if you fail to follow this. In the design,
identify all primary keys and mapping cardinalities correctly.
3) Convert all entity and relationship sets into relation schemas. You do not have to draw the
schema. Just write the schema names followed by attribute names. E.g., schema1(att1,att2,att3).
You do not need to identify foreign keys.
4) If any of the relation schema representing a relationship set is redundant, remove that. Mention
your justifications.
5) After removing redundancies, for each relation schema list a reasonable set of functional
dependencies that should exist.
Submit one pdf document that will contain:
a. E-R model drawn using Lucidchart
b. Initial set of relation schemas
C.
Reduced set of relation schemas (with explanations)
d. Functional dependencies for each schema in the reduced set
Page 2 of 2
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 3 steps with 1 images
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.Recommended textbooks for you
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education