Question

In: Computer Science

Benis firm has hired you to develop a database to keep information about its sales offices....

Benis firm has hired you to develop a database to keep information about its sales offices. It has a number of sales offices in several towns. We need to store unique office number and location for each sales office. Each sales office is assigned to one or more employees. You need to store the following information for each employee; unique employee id, employee name, salary and phone number(s). An employee must be assigned to only one sales office. For each sales office, there is always one employee assigned to manage that office. An employee may manage only the sales office to which he/she is assigned. The firm lists property for sale. Attributes of property include a unique property_Id and Location which is made up of Address, City, and Zip Code. Each property must be listed with one (and only one) of the sales offices. Each unit of property has only one owner. For each owner we need to store unique owner_id and owner name. An owner may own one or more properties, and we need to keep the percentage of properties owned by each owner.

REQUIRED:

a) List down the entities, their key attributes and foreign attributes if any.

                                                                                                          

b) Draw an E-R diagram using the above information, clearly showing the

     cardinalities and ordinalities.                                                                         

c) Differentiate between a multi-valued attribute and a composite attribute giving an

      example extracted from the passage.                                                            

                                                                                              [TOTAL: 25 Marks]

Solutions

Expert Solution

Answer 1:

Entities:

1. Employee:

Employee_id
Employee_name
Salary
Phone_number
SalesOfficeId

2. Property

Property_id
Address
City
ZipCode
listed_sales_office_to
Owner_id

3. Owner

Owner_id
Owner_name
Property_id
Property_percentage


4. Sales Office

Sales_office_id
Managed_by
Location

Answer 2:

MySQL Script

CREATE TABLE `Employee` (
        `employee_id` INT NOT NULL AUTO_INCREMENT,
        `employee_name` varchar(255) NOT NULL,
        `salary` INT(255) NOT NULL,
        `phone_number` INT(255) NOT NULL,
        `sales_office_id` INT(255) NOT NULL,
        PRIMARY KEY (`employee_id`)
);

CREATE TABLE `Sales_Office` (
        `sales_office_id` INT NOT NULL AUTO_INCREMENT,
        `sales_office_managed_by` varchar(255) NOT NULL,
        `office_location` varchar(255) NOT NULL,
        PRIMARY KEY (`sales_office_id`)
);

CREATE TABLE `Property` (
        `property_id` INT NOT NULL AUTO_INCREMENT,
        `address` varchar(255) NOT NULL,
        `city` varchar(255) NOT NULL,
        `zip_code` INT(255) NOT NULL,
        `listed_to_sales_office` INT(255) NOT NULL,
        `owner_id` INT(255) NOT NULL,
        PRIMARY KEY (`property_id`)
);

CREATE TABLE `Owner` (
        `owner_id` INT NOT NULL AUTO_INCREMENT,
        `owner_name` varchar(255) NOT NULL,
        `property_id` INT(255) NOT NULL,
        `property_percentage` FLOAT(255) NOT NULL,
        PRIMARY KEY (`owner_id`)
);

ALTER TABLE `Employee` ADD CONSTRAINT `Employee_fk0` FOREIGN KEY (`sales_office_id`) REFERENCES `Sales_Office`(`sales_office_id`);

ALTER TABLE `Sales_Office` ADD CONSTRAINT `Sales_Office_fk0` FOREIGN KEY (`sales_office_managed_by`) REFERENCES `Employee`(`employee_name`);

ALTER TABLE `Property` ADD CONSTRAINT `Property_fk0` FOREIGN KEY (`listed_to_sales_office`) REFERENCES `Sales_Office`(`sales_office_id`);

ALTER TABLE `Property` ADD CONSTRAINT `Property_fk1` FOREIGN KEY (`owner_id`) REFERENCES `Owner`(`owner_id`);

ALTER TABLE `Owner` ADD CONSTRAINT `Owner_fk0` FOREIGN KEY (`property_id`) REFERENCES `Property`(`property_id`);

Answer 3:

multi-valued attribute vs a composite attribute

A multivalue attribute can have more than one values associated with the key of an entity to it for eg: a MNC can have many offices in different locations.

A composite attribute are non atomic attributes that is composed of other attributes for eg Address can have many attributes to it such as street no, city, landmark, state, zipcode, country etc.

ALL THE BEST. THANKS.


Related Solutions

You have information about recent sales that you want to use for testing the database. 1....
You have information about recent sales that you want to use for testing the database. 1. Create the tables for the data provided. The tables should include the primary and foreign keys. Provide the SQL statements. 2. Insert the data into the tables. Provide the SQL statements. 3. Show the contents of each table. Provide the SQL statements. Customer Table Customer ID, Last Name, First Name, Street Address, City, State, Zip Code, Current Balance, Credit Limit, Sales Rep. ID Sales...
You are hired to create a database for a Real Estate firm that lists and sells...
You are hired to create a database for a Real Estate firm that lists and sells properties at multiple sales offices . Draw an ER diagram using ERDPlus for the following description of the firm, indicate all primary keys and cardinalities. List any assumptions you made. There are many sales offices in several states. Attributes of a sales office include office number (identifier) and location. Components of location include Address (number & street), City, State and Zip. Each sales office...
An engineering firm is expanding its offices in the United States. The firm has selected four...
An engineering firm is expanding its offices in the United States. The firm has selected four prime locations near major metropolitan areas. They want to determine if the average hourly wage is significantly different among the four chosen locations since this would impact overall cost. A survey of 20 wages of similar positions in each of the four locations was taken by random sampling, thus the sample size in each location was 5. The following ANOVA table was created based...
A law firm proposed the following table to keep track the information about cases and the...
A law firm proposed the following table to keep track the information about cases and the lawyers who handle the cases: CASE (caseNumber, caseDescription, lawyerInCharge, caseAssistant, beginningdate, endingDate, lawyerRate, accumulatedHours, clientsName., clientPhone, clientAdress, clientType, laywerPhone, caseResultDescription, clientCurrentPayment, paymentMethod, salary, bonus) Among above attributes, caseNumber, is the ID of the case lawyerInCharge is the name of the lawyer (a single person) who in charge of the case. NOTE : There may be also several staffs in the firm serve as the...
A firm has hired you as a consultant. This firm is perfectly competitive and has no...
A firm has hired you as a consultant. This firm is perfectly competitive and has no control over price. This firm is selling 10,000 units at a price of $3. Total costs are $40,000. Total variable costs are $35,000. They can produce another unit at a cost of about $3. What do you recommend? shut down continue to operate at a loss in the short run decrease quantity increase quantity
A certain business keeps a database of information about its customers. A. Let C be the...
A certain business keeps a database of information about its customers. A. Let C be the rule which assigns to each customer shown in the table his or her home phone number. Is C a function? Explain your reasoning.
The owner of Horne Design Wallcoverings has hired you as a consultant to design a database...
The owner of Horne Design Wallcoverings has hired you as a consultant to design a database management system for his chain of three stores that sell wallpaper and accessories. He would like to track sales, customers and employees. After initial meeting with him, you have developed a list of business rules and specifications to begin the design of an E-R Model. The list of business rules are: Customers place orders through a branch A customer may place many orders A...
For this part, assume you are entering information about transactions into their relational database. You will...
For this part, assume you are entering information about transactions into their relational database. You will be entering financial information, but you will also need to consider the other types of information Big Marker would want to know about that event. You will also utilize what you have learned to identify internal controls specific to each transaction. The transactions are as follows: a. Billed 30 communities for monthly dues of $600 (each). b. Borrowed $10,000 from First National Bank with...
1. A cosmetic product retailer needs to create a database to keep track of the information...
1. A cosmetic product retailer needs to create a database to keep track of the information for its business operations. The company has a web site that posts all its products. The product information includes product ID, product name, description, and unit price. The company also needs to keep track of customers’ information, including customer names, their shipping addresses, and the email address. The company creates an account for each customer for identification and tracking purpose. A customer can purchase...
A firm has hired you as a consultant. This firm is not perfectly competitive--it has some...
A firm has hired you as a consultant. This firm is not perfectly competitive--it has some control over prices. This firm is currently selling 1000 units, generating $10,000 in revenues and $12000 in total costs. The marginal revenue is about $4, it costs them about $5 to make another unit. Per unit variable costs are about $5 per unit. Based on your analysis, what do you recommend? shutdown operate at a loss change nothing decrease price, increase quantity increase price,...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT