Present an entity-relationship model?

Assignment Help Database Management System
Reference no: EM13923170

A company that sells and installs tiles needs a database with the requirements specified below.

Your task is to design and implement a relational database that meets all their requirements.

Please let me know if you need further clarifications on any of the requirements.

If you need to make any other assumptions, please state your assumptions explicitly.


The company has several branch offices. Each branch is identified by a branch_number. The name, address (street, city, ZIP), and revenue_target of each branch office are maintained.

Each branch has a designated branch manager and the branch manager's employee_ID is maintained.

Each employee has a unique employee_ID.

The company maintains the first name, last name, designation, starting_date, branch_number, salary, and supervisor's employee_ID for each employee.

Each product sold by the company is identified by a unique product_code.

The description, unit price, and stock_level of each product are maintained.

Each type of installation performed by the company is identified by a unique installation_type.

The description and the billing_rate per hour for the installation_type are maintained.

Customers are identified by unique customer_ID. Information is maintained on each customer's address (street, city, ZIP) and phone_number.

Each order is identified by unique order_number.

Each order is placed by a unique customer on a specific date. An employee is designated as the salesperson for each order.

A customer order may include orders for multiple products and installation services. For each product in an order, the quantity_ordered is recorded.

For each installation_type ordered, the estimated number_of_hours for installation is recorded.

Note that there may be orders that include products but no installation services.

Similarly, there may be orders that include installation services but no products.

Specific Tasks:

Present an Entity-Relationship model that meets the above requirements.

Try to ensure that your model has no many-to-many relationships.

Present a logical data model to meet the requirements where all the tables are in the third normal form.

Specify all the attributes, primary keys and foreign keys of the tables. For each attribute specify the data type and domain.

Implement the database designed in step 2 using any DBMS of your choice. Populate the tables with sample data.

Formulate SQL queries for the following:

For each salesperson, list the salesperson's Employee_ID, Name, supervisor's Employee_ID, and supervisor's name.

For each product list the Product_code, stock_level, and the total quantity ordered.

For each Order list the Order_number, order date, Employee_ID of salesperson, total amount for products, and total amount for installation.

The total amount for products is the sum of the unit price times quantity of the products ordered.

The total amount for installation is the sum of number_of_hours times the billing_rate of the installation types.

The revenue_generated by a branch is the sum of the total amount of all orders for salespersons working at that branch.

The total amount of an order is given by the sum of the total amount for products and the total amount for installation.

List the branch number, branch name, revenue_target, and the revenue_generated for each branch that fails to meet its revenue_target.

List the list the Employee_ID and name of salespersons who have sold only to customers located in the same city as the city in which the salesperson's branch is located.

List the list the Employee_ID and name of salespersons who have sold to every customer located in the same city as the city in which the salesperson's branch is located.

Reference no: EM13923170

Systems administrator for your school

Using the Web, identify the chief information officer, chief information security officer, and systems administrator for your school. Which of these individuals represents t

Discuss the importance of a procedural lifecycle

Create, and enter into the spreadsheet, a set of fictitious values that will suggest a return on investment for the new system within the next two years - Discuss why each o

Draw an entity-relationship diagram

Identify the entities, attributes, relationships, and cardinality ratios from the description Draw an entity-relationship diagram (using Sybase Power Designer) showing the i

Discuss the database administrator for department store

Imagine that you have been hired as the database administrator for a local department store. The department store has recently expanded by opening five (5) stores within you

Centralized versus decentralized database management systems

Centralized versus Decentralized Database Management Systems.Identify a business that has between 5 and 25 locations. Please respond to the following in not more than 250 word

Design an entity relationship diagram

Work Proposal Format for the Individual Assignment of Database Systems Administration (ECM38IS) - Here a realistic scenario of a relational database system is given. This dat

Write a program that uses the xml file from par display name

Write a program that uses the XML file from par (a) to display the names, states, and party affiliation of all the senators in the DataGridView in order by state. The two se

Build a gantt chart using microsoft project

Discuss what it will take to build a Web architecture, move an existing Website with minimal downtime, and provide a disaster recovery solution to ensure the site is always


Write a Review

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd