IT244 Saudi Electronic University Normalization Process Project

User Generated

Znelnz91

Programming

IT244

Saudi electronic university

Description

the course is: data base management

the left file is the questions ( named IT data base) and the right one is our project as needed in our group.

please help me to continue answering the project question starting from points 3 and 4 ,

i need it by 9 am in saudi arabia


thank you

Unformatted Attachment Preview

College of Computing and Informatics Project Deadline: Thursday 28/11/2019 @ 23.59 [Total Mark for this Project is 10] Students Details: CRN: ### Name: ### Name: ### Name: ### ID: ### ID: ### ID: ### Instructions: • • • In this file, you will find a list of database project ideas. You must choose any one of them to design and implement. You can work on this project as a group (maximum 3 students) or individually. You have to send the names of group members to your instructor by the end of week 9. This project worth 10 marks, will be distributed as mentioned below: a. Design the database, following an ER model. (4 marks) b. Normalize the tables (each table should be in at least 3NF) (2 marks) c. Use MySQL or any other database to create the normalized tables and populate your tables with at least 5 rows. d. Execute the sample requested queries. (2 marks) (2 marks) • Each one of you must submit two separate copies (one Word file and one PDF file) on Blackboard via the allocated folder. These files must not be in compressed format. The two files must contain the following: a. ER Diagram. b. All schemas before and after normalization. c. All SQL statements of: ▪ Creating tables. ▪ Inserting data in tables. ▪ Queries. d. Screenshots from MySQL (or any other software you use) of all the tables after population and queries results. • • • • • • Email submission will not be accepted which will be awarded ZERO marks. Late submissions will result in ZERO marks being awarded. The work should be your own, copying from students or other resources will result in ZERO marks. You are advised to make your work clear and well presented; marks may be reduced for poor presentation. This includes not filling your information on the cover page. You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question. Use Times New Roman font for all your answers. Project 1 Airline Reservation System For this project, following assumptions have been made: 1. The booking is only open for the next seven days from the current date. 2. Only two categories of the plane can be booked i.e. Economy and Business. 3. The total number of tickets that can be booked in each category is 10 only. 4. By ‘user’, we mean the person who is booking the ticket for the passenger(s). For example, an employee of Saudi Airline. Your project should include information about the following entities and their respective attributes: Entities Attributes User User_id User_password First_name Last_name Age Gender Email_address Contact_number City Passenger Passenger_ID Name Gender Age Phone_num BookingRef_num Seat_no Status Flight Flight_number Flight_name Departure_airport Arrival_airport Departure_time Arrival_time Availabilty_of_seats Airport Number Name Flight_number Arrival_time Departure_time Ticket Ticket_id Flight_number Booked_user Status Number_of_passengers Required SQL Queries: 1. Write a query that displays the User_id and names of all those passengers who booked ticket on any particular flight. 2. Write a query to display details (ex. Passenger_ID, name, etc.) of all passengers travelling under a particular ticket. 3. Write a query to display the time at which any Flight_number reaches any Airport_name. For example, EK233, Dammam. 4. Write a query to display all flights in ascending order of their departure time. 5. Write a query to display the flight_number that stops for the longest time at any airport (e.g., Dammam). Project 2 Payroll Management System The Payroll Management System deals with the financial aspects of an employee's salary, allowances, deductions, gross pay, and net pay etc. Consider a database to manage the payrolls of a company’s employees. Your project should include information about the following entities and their respective attributes: Entities Attributes Employee Employee_number Employee_name Gender Address Working_hours Salary Department_number Department Department_number Department_name Location Project Project_number Project_name Department_number Payment Salary Pay_scale Travelling_allowance House_allowance Deducted_tax Required SQL Queries: 1. Count the employees having salaries (excluding tax deductions) less than any particular amount (e.g., less than 5000 SAR). 2. Write a query that shows the total salaries (excluding tax deductions) paid to all employees working in a particular department. 3. Write a query that displays the salary (after tax deductions) of each employee in a particular department. 4. Write a query that displays the employees having minimum and maximum salaries (excluding tax deductions) in all departments. 5. Write a query that displays the total number of employees in all departments. Project 3 Digital Library Management System (DLMS) This project is supposed to develop a database to manage online digital library management system (A student and faculty can issue books). Your product should contain information about courses, students, teachers, departments book, authors, number of copies, number of days and details of fine. Each Book has id, title, author, number, submission date and time, duration, issue date. You should store an id, name, E-mail for each student & teachers. Each teacher has id, name, department name. For new students enrolled to university, you have to store information like Name and ID. Database should also store who issues that book and when and duration. Details of Fine (when the book is not returned at a due date) is also stored. SQL Queries: 1. Write a query to find which student has issued the Book Id =2345 in the last 4 months. 2. Find the students who submitted a book after the deadline in “health science” course. 3. List the name and email for all students who supposed to return the book, but they did not. 4. Show the total book of each course that a student has taken. 5. Show the students who have issued the book and when and duration. Detail of Fine (when the book is not returned at a time). III. Your Project: Payroll Management System 1. Design & ER Model Assumptions: 1. 2. 3. 4. One Employee works in one Department and the department may have many Employees The Department may have one project or more Many employees can have the same salary Pay-scale the range of the salary that the employee receive during his work 2. Normalize the Tables (in 3NF at least) All schemas before and after normalization will be the same: 1. Employee(Employee_number,Employee_name,Gender,Address,Working_hours,Salary,Depart ment_number) 2. Department(Department_number,Department_name,Location) 3. Project(Project_number,Project_name,Department_number) 4. Payment(Salary,Pay_scale,Travelling_allowance,House_allowance,Deducted_tax) According to all above schemas and based on the main concepts & phases of normalization:• No multi-values or composite values in 1st NF. • No partial dependency (two primary keys that one non-primary depends on one of primary key not the other) since we have one & only primary key in each schema in 2nd NF. • No transitive dependency (non-primary attribute depends on non-primary attribute) since all our attributes depend on the primary key in 3rd NF. So, all schemas in 3rd NF as we will confirm that after creating tables & inserting data in next parts of the project. 3. Create the Normalized Tables and Populate them with at least 5 Rows SQL Diagram CREATE TABLE [Employee]( [Employee_number] [int] NOT NULL, [Employee_name] [nvarchar](50) NULL, [Gender] [nvarchar](6) NULL, [Address] [nvarchar](75) NULL, [Working_hours] [real] NULL, [Salary] [money] NULL, Employee [Department_number] [int] NULL ) ON [PRIMARY] INSERT [Employee] ([Employee_number], [Employee_name], [Gender], [Address], [Working_hours], [Salary], [Department_number]) VALUES (1112, N'Mamdouh Moussa', N'male', N'Dammam', 40, 3000.0000, 100) INSERT [Employee] ([Employee_number], [Employee_name], [Gender], [Address], [Working_hours], [Salary], [Department_number]) VALUES (1113, N'Youssef Mamdouh', N'male', N'Dammam', 40, 5000.0000, 100) INSERT [Employee] ([Employee_number], [Employee_name], [Gender], [Address], [Working_hours], [Salary], [Department_number]) VALUES (1114, N'Ebrahim nadeem', N'male', N'Riyadah', 50, 8000.0000, 200) INSERT [Employee] ([Employee_number], [Employee_name], [Gender], [Address], [Working_hours], [Salary], [Department_number]) VALUES (1115, N'Dina Yakout', N'female', N'Dammam', 40, 12000.0000, 200) INSERT [Employee] ([Employee_number], [Employee_name], [Gender], [Address], [Working_hours], [Salary], [Department_number]) VALUES (1116, N'kinda Mamdouh', N'female', N'Dammam', 40, 5000.0000, 300) INSERT [Employee] ([Employee_number], [Employee_name], [Gender], [Address], [Working_hours], [Salary], [Department_number]) VALUES (1117, N'Mark Tharwat', N'male', N'Dammam', 40, 15000.0000, 100) CREATE TABLE [Department]( [Department_number] [int] NOT NULL, [Department_name] [nvarchar](50) NULL, [Location] [nvarchar](75) NULL ) ON [PRIMARY] Department INSERT [Department] ([Department_number], [Department_name], [Location]) VALUES (100, N'Programming', N'Dammam') INSERT [Department] ([Department_number], [Department_name], [Location]) VALUES (200, N'Graphic', N'Dammam') INSERT [Department] ([Department_number], [Department_name], [Location]) VALUES (300, N'Network', N'Jeddah') INSERT [Department] ([Department_number], [Department_name], [Location]) VALUES (400, N'Analysis', N'Riyadah') CREATE TABLE [Project]( [Project_number] [int] NOT NULL, [Project_name] [nvarchar](50) NULL, Project [Department_number] [int] NULL ) ON [PRIMARY] INSERT [Project] ([Project_number], [Project_name], [Department_number]) VALUES (1, N'Teach Programming', 100) INSERT [Project] ([Project_number], [Project_name], [Department_number]) VALUES (2, N'Web Application', 100) INSERT [Project] ([Project_number], [Project_name], [Department_number]) VALUES (3, N'Security System', 200) CREATE TABLE [Payment]( [Salary] [money] NOT NULL, [Pay_scale] [nvarchar](100) NULL, [Travelling_allowance] [money] NULL, [House_allowance] [money] NULL, [Deducted_tax] [money] NULL ) ON [PRIMARY] INSERT [Payment] ([Salary], [Pay_scale], [Travelling_allowance], [House_allowance], [Deducted_tax]) VALUES (3000.0000, N'Fresh Graduate', 1500.0000, 4000.0000, 300.0000) Payment INSERT [Payment] ([Salary], [Pay_scale], [Travelling_allowance], [House_allowance], [Deducted_tax]) VALUES (5000.0000, N'Supervisor and must have experience three years', 2000.0000, 4000.0000, 500.0000) INSERT [Payment] ([Salary], [Pay_scale], [Travelling_allowance], [House_allowance], [Deducted_tax]) VALUES (8000.0000, N'Manager and must have experience Six years', 3000.0000, 6000.0000, 400.0000) INSERT [Payment] ([Salary], [Pay_scale], [Travelling_allowance], [House_allowance], [Deducted_tax]) VALUES (12000.0000, N'Manager and must have experience 10 years', 5000.0000, 8000.0000, 1200.0000) INSERT [Payment] ([Salary], [Pay_scale], [Travelling_allowance], [House_allowance], [Deducted_tax]) VALUES (15000.0000, N'Manager and must have experience 20 years', 70000.0000, 100000.0000, 1500.0000) 4. Write the sample requested Queries & Execute them 1- Count the employees having salaries (excluding tax deductions) less than any particular amount (e.g., less than 5000 SAR). select count(*) as [employees having salariesless than 9000] from Employee where Salary Stu_ID Teach_Name,Teach_Email,Teach_Dept_ID, Teach_Dept_Name => Teach_ID Book_Title,Book_NO, Book_No_Copy => Book_ID For_Course_Name => For_Course_ID For_Cour se_ID(PK) For_Cour se_Name Issue _Date Issue _time Issu e_b y Issue_s ub_date Issue_Su b_Time Issue_D uration Issue_Ret urn_Date Fine_ Amon t Extracted tables : Student Table (2NF) Stu_ID(PK) Stu_Name Stu_Email Teacher Table (But Not in 2NF yet) Teach_Id(PK) Teach_Name Teach_Eamil Teach_Dept_ID Book_title Book_No Book_No_Copy Teach_Dept_Name Book Table(2NF) Book_ID(PK) Course Table(2NF) For_Course_ID(PK) For_Course_Name All table generated are in 2NF, except Teacher Table. Teach_Dept_Name is dependent on Teach_Dept_ID. So we extract a new Table called Department Table Teacher Table(1NF) Teach_Id(PK) Teach_Name Teach_Dept_Name => Teach_Dept_ID Department Table (2NF) Teach_Dept_ID(PK) Teach_Dept_Name Teach_Eamil Teach_Dept_ID Teach_Dept_Name ALL Tables in 2NF. Issue Table(2NF) Stu_ID(PK) Teach_Id(PK)(FK) Book_ID(PK)(FK) For_Course_ID(PK)(FK) Issue_Date Issue_time Student Table(2NF): Stu_ID(PK) Stu_Name Stu_Email Teach_Name Teach_Eamil Teach_Dept_ID(FK) Book_No Book_No_Copy Teacher Table(2NF): Teach_Id(PK) Department Table(2NF): Teach_Dept_ID(PK) Teach_Dept_Name Book Table(2NF): Book_ID(PK) Book_title Course Table(2NF): For_Course_ID(PK) For_Course_Name Issue_by Issue_sub_date Issue_Sub_Time Issue_Duration Issue_Return_Date Fine_Amont D- Third Normal Form (3NF) : A table is said to be in the Third Normal Form when, 1. It is in the Second Normal form. 2. And, it doesn't have Transitive Dependency. All tables are already in 3NF, except the Issue Table. Issue Table (2NF): Stu_ID(PK) Teach_Id(PK)(FK) Book_ID(PK)(FK) For_Course_ID(PK)(FK) Issue_Date Issue_time Issue_by Issue_sub_date Issue_Sub_Time Issue_Duration Issue_Return_Date The following fields have Transitive Dependency. (Issue_Time , Issue_by, Issue_Duration,Issue_Sub_Date,Issue_Sub_Time) have Transitive Dependency on Issue Data So we extract them into a new table Issue_Event table. Issue Event table (2NF): Issue Id(PK) Issue_Date Issue_time Issue_by Issue_sub_date Issue_Sub_Time Issue_Duration Also( Issue_Return_Date, Fine_Amount) have Transitive Dependency on Submission and Return Data. So we Extract a new table called “Return Table”. Return Table (3NF): Return_ID(PK) Issue_Return_Date Fine_Amont And Table Issue Table(3NF) will be: Stu_ID(PK) Teach_Id(PK)(FK) Book_ID(PK)(FK) For_Course_ID(PK)(FK) Issue_ID(FK) Fine_Amont Issue_Event Table (3NF) ends as : Issue Id(PK) Issue_Date Issue_time Issue_by Issue_sub_date Issue_Sub_Time Issue_Duration Return_ID(FK) One last step is reviewing table Issue Table, We can use Issue_Id as a primary Key of the table instead of the combined columns, as it is uniquely identify the row. And Issue Table(3NF) will be: Issue_ID(PK)(FK) Stu_ID(FK) Teach_Id(FK) Book_ID(FK) For_Course_ID(FK) Further review suggested that, after using Issue_ID as Primary Key, we can join the two table Issue_Table + Issue_Event Table using generalization into one table. So And end up transferring the fields of Issue_Event to Issue Table(3NF) will be: Issue_ID(PK) Stu_ID(FK) Teach_Id(FK) Book_ID(FK) For_Course_ID(FK) Issue_Date Issue_time Issue_by Issue_sub_date Issue_Sub_Time sue_Duration Return_ID(FK) ALL Tables in 3NF . Issue Table(3NF) Issue_ID(PK) Stu_ID(FK) Teach_Id(FK) Book_ID(FK) For_Course_ID(FK) Issue_Date Issue_time Student Table(3NF) Stu_ID(PK) Stu_Name Stu_Email Teach_Name Teach_Eamil Teach_Dept_ID(FK) Book_No Book_No_Copy Teacher Table(3NF) Teach_Id(PK) Department Table(3NF) Teach_Dept_ID(PK) Teach_Dept_Name Book Table (3NF) Book_ID(PK) Book_title Issue_by Issue_sub_date Issue_Sub_Time sue_Duration Return_ID(FK) Course Table (3NF) For_Course_ID(PK) For_Course_Name Return table (3NF) Retru_ID(PK) Issue_Return_Date Fine_Amont
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

At...


Anonymous
Awesome! Made my life easier.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4
Similar Content
Related Tags