System Database Design Assignment

28-10-17 Rochio Varela 0 comment

This solution is about System Database Design Assignment

Task 1 – Normalization

Assumptions: Currently the beauty salon manages the customer appointment and staff with the help of a single spreadsheet. It manages all the data of the staffs and customers in the spreadsheet which causes many issues like duplication, repetition of data, etc. We develop the database and store all the current data of the salon in the one table then we found the issues have still arrived, so we start the normalization of the database table in which data are stored. Currently, the salon identifies the customer by their phone number, the customer cannot have multiple appointments at the same time, and the staff member cannot have the multiple appointments at the same time. At first, we create the table name as “salon_appointment” in which we take total 7 attributes name as Appointment_ID this is the primary key in this table, appointment_Date, Staff_id, Staff_name, Customer_Phone, Customer_name and Appointment description. Currently, this table is in 0NF form. Now we start to normalize this table in the 1NF form of normalization.

 System Database Design Assignment, programming assignments help, programming assignment help, database assignment help, computer network assignment help, Object Oriented Programming

Figure 1 0NF

To normalize this table in the 1NF form we divide the Salon_appointment table in the two different table named as “Customer” and Appointment_detail table. In the customer table there are two attributes the first is ‘Customer_phone’ this is the primary key and in the “Appointment_detail” table there is 6 attribute name as  ‘Appointment_id’ this is the primary key in this table, appointment_date, staff_id, ‘customer_phone’ it is the foreign key in this table comes from customer table, appointment_description.  We made the one-to-one relationship between both the table so that we can determine the customer who has an appointment in the salon. By fetching the customers phone number from the customer table. It helps us to identify the customer who has more than one appointment.

Figure 2 1NF

After this, we normalize the existing relational database table into the second normalized form of normalization. By separating the staff and appointment information to determine the staffs, who have more than one appointment. To normalize this database in 2NF form, we divided the “Appointment_detail” into the two separate table name as “Staff” and “Appointment” table. In the staff table, there are three attributes name as Staff_ID which is the primary key of this table, Staff_FName, and Staff_LName. And in the Appointment table, there are 5 attributes name as Appointment_ID, this is the primary key, Appointment_date, Staff_Id, Customer_phone, and Appointment_description. In this form of normalization, we make the one to many relationships between the database tables by defining the Staff_id and Customer_phone as the foreign key in the appointment table. It helps to identify the staff and customer in the appointment detail who has more than one appointment at the same time.

System Database Design Assignment, programming assignments help, programming assignment help, database assignment help, computer network assignment help, Object Oriented Programming

Figure 3 2NF

After this, we normalize the table into the third normal formal (3NF) form of normalization. To normalize the table in the 3NF form we divided the appointment table into the two different table name as “Appointment” and “Appointment_detail” table. There is 3 attribute in the Appointment table named as Appointment_ID, this is the primary key, Staff_ID, custome_phone. The staff_id and Customr_phone is the foreign key in the appointment table. And in the Appointment_detail table, there is 3 attribute named as Appointment_id, this is the primary key, appointment_date, and Appointment_description. This helps us to check the appointment detail of all the staffs and customer according to a different date.
System Database Design Assignment, programming assignments help, programming assignment help, database assignment help, computer network assignment help, Object Oriented Programming

Figure 4 3NF

Read more about Database Management Assignments Help

Physical E-R diagram

We show the ER-diagram of the database, in which we show the relationship between each table. In the following ER-diagram, we show the customer table, staff table and appointment detail table linked with the Appointment table. The relationship between all the tables is one-to-many.

System Database Design Assignment, programming assignments help, programming assignment help, database assignment help, computer network assignment help, Object Oriented ProgrammingFigure 5 ER Diagram

Task 2 – Advanced Normalization

To normalize the call details table, we first made a table “Bill_Tax_invoice_detail”. This table is in 0NF form of normalization. There are total 13 attributes in this table named as Tax_invoice_id, this is the primary key, Billing_period, payment_due_by, Amount_due, Customer_number, Cutomer_address, Call_date, phone_number, call_type, duration, charges, connection_charges, charge_per_30_second.

System Database Design Assignment, programming assignments help, programming assignment help, database assignment help, computer network assignment help, Object Oriented ProgrammingFigure 6 0NF

After this, we normalize this table in the 1NF form. To normalize this table in 1NF form, we divide this into the two different separate table named as “Customer_invoice_call_detail” and “Call_type_charges” tables. There are total 9 attributes in the “Customer_invoice_call_detail” table named as Invoice_call_id, this is the primary key, Billing_period, Payment_Due_by, Amount_due, Customer_number, Customer_address, Call_date, Phone_number, Call_type, Duration, and charges. The Call_type is the foreign key in this table to fetch the charge information of the type of call made by the customer. In the Call_type_charges table total, 3 attributes are made named as Call_type, this is the primary key, connection_charges, Charge_per_30_second.

System Database Design Assignment, programming assignments help, programming assignment help, database assignment help, computer network assignment help, Object Oriented ProgrammingFigure 7 1NF

After this, we normalize the tables into 2NF form. To normalize this in 2NF, we divided the Customer_invoice_call_detail table into the two different table named as “customer” and “invoice_call_detail” tables. There are total 6 and 10 attributes in the “Customer” and “Invoice_call_detail” table respectively. The Customer_number and Invoice_number is the primary key in the Customer and Invoice_call_detail table respectively. The Customer_number is the foreign key in the Invoice_call_detail table.

System Database Design Assignment, programming assignments help, programming assignment help, database assignment help, computer network assignment help, Object Oriented ProgrammingFigure 8 2NF

After this, we normalize the table into the 3NF form of normalization. We divide the Invoice_call_detail table into two different table named as “invoice” and “call_detail” table. There is total 5 and 7 attributes in the “Customer” and “Invoice_call_detail” table respectively. The Invoice_number and call_detail_id  is the primary key in the Invoice and Call_detail table respectively. The Invoice_number is the foreign key in the Invoice_call_detail table.

System Database Design Assignment, programming assignments help, programming assignment help, database assignment help, computer network assignment help, Object Oriented ProgrammingFigure 9 3NF

Read More About Software Requirement Specification Assignment Help

Physical E-R diagram

The ER-diagram given below shows the data flow and relationship between the tables of the database. The Call_type_charges, Customer, and invoice are the different table of the database which is connected to the Call_detail table by the one-to-many relationship table.

System Database Design Assignment, programming assignments help, programming assignment help, database assignment help, computer network assignment help, Object Oriented ProgrammingFigure 10 ER Diagram

Read more about Oracle Database Assignment Help

Task 3 – Entity-Relationship Modeling

In the Airline database, we made total 6 tables to store the different information of airport, flight, etc. The ER-diagram has given below shows all the tables, attributes of tables, the primary and foreign key used in the tables and the relationship between all the tables of the database.

The Tables of airline database and their attributes are shown in the table given below. The primary and foreign keys used in the system are also shown in that:

Table Name Attributes Name
Model Model_number (Primary Key)

Model_name

Range

Cruise_speed

Flight_instance Flight_instance_id (Primary Key)

Plane_id (Foreign Key)

Flight_number (Foreign Key)

Leave_datetime

Arrive_datetime

Plane Plane_id (Primary key)

Registration_no.

Built_year

Class

Passenger_capacity

Model_number (foreign key)

Flight Flight_number (Primary key)

Airport_flight_depart (foreign key)

Airport_flight_arrive (foreign key)

distance

Airport Airport_id (primary key)

Airport_name

Contact_number

Latitude

Longitude

Country_code (foreign key)

Country Country_code (Primary key)

Country_name

We made the many to many relationships between the database tables.

 Logical E-R diagram

System Database Design Assignment, programming assignments help, programming assignment help, database assignment help, computer network assignment help, Object Oriented Programming

Physical E-R diagram

System Database Design Assignment, programming assignments help, programming assignment help, database assignment help, computer network assignment help, Object Oriented Programming

Task 4 – Advanced Entity-Relationship Modeling

To extend the airline database to store the information of pilot and flight attendant, we add total 5 more table in the existing database of the airline table.

All the tables, their attributes and the primary key and foreign key used in the extended database table of the airline database design are given below:

Extended Airline database tables:

Table Name Attributes Name
Model Model_number (Primary Key)

Model_name

Range

Cruise_speed

Flight_instance Flight_instance_id (Primary Key)

Plane_id (Foreign Key)

Flight_number (Foreign Key)

Leave_datetime

Arrive_datetime

Plane Plane_id (Primary key)

Registration_no.

Built_year

Class

Passenger_capacity

Model_number (foreign key)

Flight Flight_number (Primary key)

Airport_flight_depart (foreign key)

Airport_flight_arrive (foreign key)

distance

Airport Airport_id (primary key)

Airport_name

Contact_number

Latitude

Longitude

Country_code (foreign key)

Country Country_code (Primary key)

Country_name

Pilots Pilot_id (primary key)

Pilot_fname

Pilot_lname

Date_of_birth

No._hours_own

Pilot_model_plane Pmp_id (primary key)

Pilod_id (foreign key)

Model_number (foreign key)

Flight_attendent attendent_id (Primary key)

attendant_fname

attendant_lname

date_of_birth

hiring_date

attendant_mentor

Flight_attendent_aboard Aboard_id (primary key)

Attendant_id (foreign key)

Flight_instance_id (foreign key)

Service_manager_id (foreign key)

Flight_service_manager Service_manager_id (primary key)

Manager_fname

Manager_lname

Date_of_birth

 

The relationship established between the airline’s databases tables is a many-to-many relationship.

Read more about Web Database Integration Assignment

Logical E-R diagram

 System Database Design Assignment, programming assignments help, programming assignment help, database assignment help, computer network assignment help, Object Oriented Programming

 

Physical E-R diagram

System Database Design Assignment, programming assignments help, programming assignment help, database assignment help, computer network assignment help, Object Oriented Programming

Programming Assignments Help is the best assignment help provider in the United Kingdom. Our online assignment writing help UK is specially dedicated for the students studying in all UK colleges and universities. Submit assignment to get the best quality assignment help.