In: Computer Science
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]
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.