Description
Subject Data base check files if u can do it
IFB105 Database Management
Project - Part B
Project overview
This IFB105 project gives you an opportunity to apply the concepts and skills you acquire in the unit to a ‘realistic’ database design scenario and reflect on the data requirements of an organisation.
The submission is divided into two parts due at different times during the semester. These will cover:
- Design of a database
- Creation and use of databases
The tasks for Part B
For Assessment 2B you will be required to:
- Create a database for the fictitious Company database
- Query the Addition College Database (supplied)
- Normalise a relation.
Weighting
Part B is worth 25 marks, for 25% of the unit.
Groups
You can complete this assignment individually or in pairs. You do not have to work in the same pairs as you did for Part A.
If you choose to work in a pair, only one student should submit the assignment. Please provide the name and student number of the person you worked with in the README.txt file. No consideration will be given to students who claim they did more work in their pair than the other student because this assignment can be done individually.
Doing the assignment in undeclared groups, or groups larger than two students, will be treated as plagiarism. Pairs that work together and then split due to difficulties must not submit any of the same work, or it will be treated as plagiarism.
Due date
Friday 16 October at 11.59 pm
Submission
You must submit 4 files in a ZIP file using the submission link in Blackboard:
- SQL script (a text file with the file extension changed to sql) containing your solution to task 1.
- SQL script containing your solutions for tasks 2, 3, 4 and 5 (do not upload the database import script as part of your solution).
- Word or PDF containing your solution to task 6.
- README.txt containing your full name, student number (and the name and student number of your partner if working in pairs) and a list of any queries you have attempted but were not able to successfully run in Workbench.
If you do not follow these submission guidelines you will lose a mark. Scripts in different file types will not be accepted.
Late submission
Assessment work submitted after the due date will be marked only with an approved extension (MOPP E/6.8.2). Assessment work submitted after the due date without an approved extension or, where an extension has been granted, after the extended due date, will not be marked and a grade of 1 or 0% will be awarded against the assessment item. The unit outline sets out the requirements surrounding extensions (including for disabilities), penalties for late submissions and appeals.
Please note, extensions will not be granted for group (2 members) submissions, regardless of the reason.
Assessment 2B tasks
For this assessment, you will:
- Build a script that will create a database for a given relational schema (Week 7)
- Provide the SQL commands needed to retrieve the required data using the Addition College schema (Week 8 to 10)
- Provide the commands to modify (INSERT, UPDATE & DELETE) the data using the Addition College relational schema (Week 7)
- Provide the commands needed to create appropriate indexes and views (Weeks 7 to 10)
- Provide advice on the basic security measures that should be implemented (Week 10 and 11)
- Normalise a given relation (Week 6).
Task 1 [4 marks] Company Database
An SQL script is a set of SQL commands saved as an SQL file. If you are already working with MySQL, you can either execute an SQL script file using the source command or import it in Workbench.
Write an SQL script to create a database to match the Rmap provided below. Your script MUST execute in MySQL Workbench without errors to receive full marks. You can ensure that by executing your commands in the correct order as well as avoiding the use of non-MySQL functions such as CHECK.
Marks will be awarded for the following:
- Creating the database and successfully creating new tables including all attributes (1 mark)
- Including constraints (1 mark)
- Correctly creating Primary Keys (1 mark)
- Correctly creating Foreign Keys (1 mark)
Rmap of a given relational schema to create a database for Task 1
Employee_Project (Ssn, Pnumber, Hours)
Emplyee_Car (Ssn, Car)
Car (Registration_number, model, maker, year)
Project (Pnumber, Pname, Plocation)
Employee (Ssn, Ename, Bdate, Address, Dcode, [Driver_license])
Department (Dcode, Dname, Dmg_ssn)
Notes
Task 2 [10.5 marks] using the AdditionCollege database
For task 2, we have provided you with the creation script for the AdditionCollege database. Run this script in MySQL Workbench to create the database. You should execute your query solutions to extract the necessary information.
The script is based on the following schematic:
Unit (Unit_code, Staff_id, [UnitName])
Staff (Staff_id, StaffName, Position, Gender)
Taught_by (Unit_code, Staff_id, weekday)
Student (Student_id, Student_name, Address, Gender)
TuteGroup (TuteGroup_code, Unit_code, DayHrCode, Room_Nr)
TuteGroup_List (TuteGroup_code, Student_id)
Notes
- Units are taught between Monday to Friday.
- Staff position should be “Tutor”, “Lecturer” or “Senior Lecturer”.
- Staff and student gender should be “M”, “F”, or “O”.
- Unit codes are 3 letters and then 2 numbers.
- Student and staff names include both first name and surname.
- Day hour code is the Day and time code.
- Tute group codes are the combination of numbers and letters.
Query 1 (1 mark)
Write a query to list the student ID, name, gender of students whose gender is “M”.
Query 2 (1.5 marks)
Write a query to list the staff ID of all staffs that have taught more than two units.
Query 3 (1.5 marks)
Write a query to list the names of all students of the unit with the unit code “TNS01”.
Query 4 (2 marks)
Write a query to determine the total number of different students that the staff with staff_id “S1” is teaching.
Query 5 (2 marks)
Write a query to list all students’ information who attended Monday classes. Sort the result set with the ascending order of address.
Query 6 (2.5 marks)
Write a query to list all staffs’ information who have taught more than 6 students. Please note that the same student in a different unit will be counted as different students.
Task 3 [3 marks]
Insert (1 mark)
Write an INSERT command to insert the data of a new student into student table. You need to analyze existing student id information to decide this new student’s ID. The name of the new student is ‘Sybil Stark’. The address of the new student ‘302/28 Dengate Lane, St Lucia, QLD 4067’. The new student is a female.
Delete (1 mark)
Write a DELETE command to remove the data of the staff from the staff table, who do not teach any units.
Update (1 mark)
Write an UPDATE comment to change the position of the staff with the last name ‘Rostov’ to ‘Senior Lecturer’.
Task 4 [1.5 marks]
Create Index (0.5 marks)
Currently, the database only contains a small number of records. However, the data contained within it is expected to grow significantly in the future. Creating indexes on commonly searched columns is a way performance issues can be minimized.
Write a command to create an index on student_name column of the student table.
Create view – 1 mark
Write a command to create a view to list the student ID and student name of students who have classes at room ‘RM123’.
Task 5 [2 marks]
Working as a Database Administrator for MySQL AdditionCollege database, write the following commands for two users, Vanessa and Jessica to achieve the following database security requirements:
- User Vanessa is no longer allowed to add data to the Staff table (0.5 marks)
- User Vanessa is no longer allowed to delete records from the Staff table (0.5 marks)
- User Jessica must be able to add records to the TuteGroup table (0.5 marks)
- User Jessica must be able to remove records from the TuteGroup table (0.5 marks)
Assume usernames of users namely Vanessa and Jessica are vanessa and jessica respectively.
Task 6 [4 marks]
Hint: Going through the examples of functional dependencies and normalization covered in the Week 6 Lecture will help you to work on this question.
Using the following table structure, identify all functional dependencies and then decompose this table into a set of 3NF relations. Your answer should:
- List the functional dependences
- Convert the relational schema for the relations in 3NF.
| INV_ NUM | PROD_NUM | SALE_DATE | PROD_ DESCRIPTION | VEND_ CODE | VEND_ NAME | NUMBER_SOLD | PROD_PRICE |
| 12345 | EE-A3422Z | 22_JUL_2020 | Camera | 502 | City Shop | 5 | $69.95 |
| 12345 | AC-2002X | 22_JUL_2020 | Cable | 502 | City Shop | 6 | $3.45 |
| 12345 | SV-4534Y | 22_JUL_2020 | Keyboard | 609 | COVID-19 Shop | 5 | $39.99 |
| 12346 | EE-A3422Z | 22_JUL_2020 | Camera | 502 | City Shop | 3 | $69.95 |
| 12347 | BC-7783P | 23_JUL_2020 | Monitor | 257 | Online Shop | 5 | $87.75 |
End of Document