Info6001 Database Management-Eer Diagram And Assessment Answers
- Revised content of assignment 1: Requirement Specification, EER Diagram and Data Dictionary, Transaction Requirements, Business Rules
* Requirements Specification (Clarity and completeness)
* EER Diagram and Data Dictionary
+Rightness & Completeness of EER model +Data dictionary and documentation* Transaction Requirements
- Mapping of the EER model to the relational model in DBDL
+Mapping of each relation & attributes
+Mapping of each relationship
- Normalizing the relational schema to Boyce-Codd Normal Form
+Discussion of functional dependencies, and process of normalising up to BCNF for all relations (e.g., if a relation is in 2nd normal form, give your reason why it is in 2nd form, then show all the process to normalise it up to BCNF).
Answer:
Discount:
CREATE TABLE Discount
( discount_code VARCHAR(20) NOT NULL ,
strat_date DATE NOT NULL ,
end_date DATE NOT NULL ,
discount_percentage INT(3) NOT NULL ,
requiremnet VARCHAR(20) NOT NULL ,
description VARCHAR(200) NOT NULL ,
PRIMARY KEY (discount_code))
Driver:
CREATE TABLE DRIVER
( empployee_code VARCHAR(20) NOT NULL ,
driver_code VARCHAR(20) NOT NULL ,
order_code VARCHAR(20) NOT NULL ,
date DATE NOT NULL ,
payment DECIMAL(10,2) NOT NULL ,
Name VARCHAR(20)NOT NULL ,
bank_code VARCHAR(20) NOT NULL ,
address_code VARCHAR(20) NOTNULL ,
account_number INT(20) NOT NULL ,
PRIMARY KEY (empployee_code, driver_code)
FOREIGN KEY (bank_code) REFERENCES bank_info (bank_code)
FOREIGN KEY (address_code) REFERENCES address (address_code))
Item Order:
CREATE TABLE Item_Order
( item_code VARCHAR(20) NOT NULL ,
order_code VARCHAR(20) NOT NULL ,
quantity INT(10) NOT NULL ,
PRIMARY KEY(item_code, order_code)
FOREIGN KEY (order_code) REFERENCES Phone_call_order (order_code)
FOREIGN KEY (order_code) REFERENCES Walkin_call_order (order_code))
Item:
CREATE TABLE
Item
( item_code VARCHAR(20) NOT NULL ,
Name VARCHAR(20) NOT NULL ,
size INT(5) NOT NULL ,
price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (item_code))
Ingredient:
CREATE TABLE
Ingredient
( ingredient_code VARCHAR(20) NOT NULL ,
name VARCHAR(20) NOT NULL ,
type VARCHAR(20) NOT NULL ,
price DECIMAL(10,2) NOT NULL ,
description VARCHAR(300) NOT NULL ,
stock_level VARCHAR(5) NOT NULL ,
quantity INT(6) NOT NULL ,
last_stock_taken_date DATE NOT NULL ,
suggested_stock VARCHAR(20) NOT NULL ,
PRIMARY KEY(ingredient_code))
Ingredient Order:
CREATE TABLE
Ingredient_order
( ingredient_code VARCHAR(20) NOTNULL ,
order_code VARCHAR(20) NOT NULL ,
quantity INT(10) NOT NULL ,
PRIMARY KEY (order_code, ingredient_code)
FOREIGN KEY (order_code) REFERENCES Order (order_code))
ORDER:
CREATE TABLE Order
( order_code VARCHAR(20) NOT NULL ,
supplier_code VARCHAR(20) NOT NULL ,
date DATE NOT NULL ,
total_amount DECIMAL(10,2) NOT NULL ,
PRIMARY KEY (order_code)
FOREIGN KEY (supplier_code) REFERENCES Supplier (supplier_code))
Supplier:
CREATE TABLE Supplier
( supplier_code VARCHAR(20) NOT NULL ,
name VARCHAR(20) NOT NULL ,
address_code VARCHAR(20) NOT NULL ,
contact INT(10) NOT NULL ,
PRIMARY KEY (supplier_code))
3. Normalization up to BCNF:
- Hourly _Emp (empployee_code, hourly_emp_code,Name, address_code, bank_code, account_number)
Hourly _Emp (empployee_code, Name, address_code, bank_code, account_number)
- Driver (empployee_code, driver_code, order_code, date, payment, Name, address_code, bank_code, account_number)
Driver (empployee_code, order_code, date, payment, Name, address_code, bank_code, account_number)
- Phone_call_order (order_code, phone_call_order_code, consumer_code, discount_code, status, subtotal, payment_method, tax, total_amount, description)
Phone_call_order (order_code, consumer_code, discount_code, status, subtotal, payment_method, tax, total_amount, description)
- Walkin_Call_Order (order_code, consumer_code, discount_code, status, subtotal, payment_method, tax, total_amount, description)
Walkin_Call_Order (order_code, consumer_code, discount_code, status, subtotal, payment_method, tax, total_amount, description)
- Phone_call_order (order_code, consumer_code, status, payment_method, tax description)
Walkin_Call_Order (order_code, consumer_code, status, payment_method, tax, description)
Payment (discount_code, consumer_code, subtotal, total_amount)
4. DBDL of Revised Database:
Phone call order:
CREATE TABLE phone_call_order (
order_code varchar(20) NOT NULL,
consumer_code varchar(20) NOT NULL,
status varchar(10) NOT NULL,
payment_method varchar(10) NOT NULL,
tax decimal(10,2) NOT NULL,
description varchar(200) NOT NULL
PRIMARY KEY (order_code)
FOREIGN KEY (consumer_code) REFERENCES customer (consumer_code))
Walkin call order:
CREATE TABLE walkin_call_order (
order_code varchar(20) NOT NULL,
consumer_code varchar(20) NOT NULL,
status varchar(10) NOT NULL,
payment_method varchar(20) NOT NULL,
tax decimal(10,2) NOT NULL,
description varchar(200) NOT NULL
PRIMARY KEY (order_code)
FOREIGN KEY (consumer_code) REFERENCES customer (consumer_code))
Hourly Employee:
CREATE TABLE hourly_emp (
empployee_code varchar(20) NOT NULL,
Name varchar(20) NOT NULL,
address_code varchar(20) NOT NULL,
bank_code varchar(20) NOT NULL,
account_number varchar(20) NOT NULL
PRIMARY KEY (order_code)
FOREIGN KEY (address_code) REFERENCES address (address_code)
FOREIGN KEY (bank_code) REFERENCES bank_info (bank_code))
Driver:
CREATE TABLE driver (
empployee_code varchar(20) NOT NULL,
order_code varchar(20) NOT NULL,
date date NOT NULL,
payment decimal(10,2) NOT NULL,
Name varchar(20) NOT NULL,
bank_code varchar(20) NOT NULL,
address_code varchar(20) NOT NULL,
account_number int(20) NOT NULL
PRIMARY KEY (order_code)
FOREIGN KEY (address_code) REFERENCES address (address_code)
FOREIGN KEY (bank_code) REFERENCES bank_info (bank_code))
Payment:
CREATE TABLE payment (
consumer_code varchar(20) NOT NULL,
discount_code varchar(20) NOT NULL,
subtotal decimal(10,2) NOT NULL,
total_amount decimal(10,2) NOT NULL
PRIMARY KEY (order_code, discount_code)
FOREIGN KEY (order_code) REFERENCES phone_call_order (order_code)
FOREIGN KEY (order_code) REFERENCES walkin_call_order (order_code)
FOREIGN KEY (consumer_code) REFERENCES consumer (consumer_code))
Bibliography:
Alabbasi, S., Ahmed, A., Kaneko, K., Rebeiro-Hagrave, A. and Fukuda, A., 2014. Data types managed database design for dynamic content: A database design for Personal Health Book system. In TENCON 2014-2014 IEEE Region 10 Conference (pp. 1-5). IEEE.
Al-Brashdi, A., Butler, M., Rezazadeh, A. and Snook, C., 2016. Tool support for model-based database design with Event-B.
Arauz, J., 2014. CAP limits in telecom subscriber database design. Proceedings of the VLDB Endowment, 7(13), pp.1474-1483.
Bartolini, S., Becerril, L. and Martí, J., 2014. A new Volcanic managEment Risk Database desIgn (VERDI): Application to El Hierro Island (Canary Islands). Journal of Volcanology and Geothermal Research, 288, pp.132-143.
Bugiotti, F., Cabibbo, L., Atzeni, P. and Torlone, R., 2014. Database design for NoSQL systems. In International Conference on Conceptual Modeling (pp. 223-231). Springer International Publishing.
Chende?, V., B?lteanu, D., MICU, D., SIMA, M., Ion, B., GRIGORESCU, I., PERSU, M. and DRAGOT?, C., 2015. A database design of major past flood events in Romania from national and international inventories. In Proceedings of the Conference: Air and Water Components of the Environment, Cluj-Napoca (pp. 25-32).
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management. Cengage Learning.
Buy Info6001 Database Management-Eer Diagram And Assessment Answers Online
Talk to our expert to get the help with Info6001 Database Management-Eer Diagram And Assessment Answers to complete your assessment on time and boost your grades now
The main aim/motive of the management assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignments. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at urgenthomework.com are so much skilled, capable, talented, and experienced in their field of programming homework help writing assignments, so, for this, they can effectively write the best economics assignment help services.
