Part A copy your diagram from Assignment 1 submissionAll components must be visible. Marker can zoom in.
Database Systems
Part A copy your diagram from Assignment 1 submissionAll components must be visible. Marker can zoom in.
EERD from Assignment 1 Submission
[paste image here]
Updated EERD of Assignment 1 Submission (remove this part if irrelevant)
[paste image here]
Reason(s) of update:
Part A Task 1 - List of relations
Must show the primary key, foreign key (if any), and working of any normalisation required.
Sample: MyTable (ID (pk), Name, Address, Major (fk))
Part A Task 2 - Non-trivial functional dependency of Timesheet
Must show the dependency in the either functional dependency notation or diagram
Timesheet (StaffID, FamilyName, OtherNames, TimesheetID, Date, StartTime, EndTime, StoreID, Location, State, RoleCode, RoleName, Rate, Total, TotalPay)
Sample (remove this prior to submission):
W is the determinant and XY the dependant of W; X is the determinant and Z is the dependant of X
Using notation:
W XY
X Z
W X Y Z
18346641749972216749172720148292816783300 Using diagram:
2554569259715181548825481000
Part A Task 3 - Normalisation of Timesheet
Must show the working on each normal form
Current highest normal form:
1NF (remove if irrelevant)
Resolving.
Relation(s)
2NF (remove if irrelevant)
Resolving.
Relation(s)
3NF (remove if irrelevant)
Resolving.
Relation(s)
BCNF (remove if irrelevant)
Resolving.
Relation(s)
Part B Task 1 and 2 - Additional tables and populations
Show proof of testing
List of assumptions:
Table: (use this set for each table)
[Paste the SQL statement used]
[Paste screenshot of result]
Part B Task 3 - Chosen queries
Show proof of testing
[choose one between Q1 or Q2, if more than one is provided, only the first one will be marked; remove irrelevant set]
Q1. List of non-shopping centre locationscontaining the location's full address, the area size, and the potential seating capacity (assuming that min. service area is 15 sqm and 1.5 sqm per seating), e.g. for 125 sqm area, the area available for seating is 110 sqm, therefore can hold 73 seats.
SQL statement
[insert your SQL statement for Q1 here]
Proof of testing
[paste a screenshot of the query result here]
Q2. Upcoming manager's performance review overviewcontaining the manager's full name and email address, also the number of staff they managed. The report should only capture managers with the next review date within the next 30 days.
SQL statement
[insert your SQL statement for Q2 here]
Proof of testing
[paste a screenshot of the query result here]
[choose two between Q3, Q4, or Q5, if more than two is provided, only the first two will be marked; remove irrelevant set]
Q3. Casual staff contact directorycontaining their full contact details (full name, phone number, complete address, and date of birth) sorted alphabetically based on the full name.
SQL statement
[insert your SQL statement for Q3 here]
Proof of testing
[paste a screenshot of the query result here]
Q4. List of shopping centre stores with sit-down servicecontaining the company's name, store's name, shopping centre's name and full address, all the stores located within a shopping centre that offer sit-down service in a specific state (choose one). The list should be sorted alphabetically based on the company's name, store's state, suburb, and name.
SQL statement
[insert your SQL statement for Q4 here]
Proof of testing
[paste a screenshot of the query result here]
Q5. List all staff of one of the companies(choose one) containing their full name, phone number and start date, who has never worked for Krusty Burger and is not a manager.
SQL statement
[insert your SQL statement for Q5 here]
Proof of testing
[paste a screenshot of the query result here]
[choose two between Q6, Q7, or Q8, if more than two is provided, only the first two will be marked; remove irrelevant set]
Q6. Yearly total part-time and full-time staff wage overviewcontaining the company's name and the total yearly wage of all staff. Part-time staff yearly wage can be calculated based on agreed minimum hours per week, the hourly rate plus the superannuation. There are 52 weeks in a year. Full-time staff yearly wage is the salary amount.
SQL statement
[insert your SQL statement for Q6 here]
Proof of testing
[paste a screenshot of the query result here]
Q7. List of long-time staff with multiplecontractscontaining their full name, the number of contracts, and the earliest start date. Only show staff with more than one contract and have worked for any company in Acquisition Co longer than ten years.
SQL statement
[insert your SQL statement for Q7 here]
Proof of testing
[paste a screenshot of the query result here]
Q8. Large acquisition overviewcontaining the company's name, the number of stores, and the number of staff. A company is considered a large acquisition if it has more than 25 stores or more than 100 staff.
SQL statement
[insert your SQL statement for Q8 here]
Proof of testing
[paste a screenshot of the query result here]
