This is our database final project called Sigma Salon.
Part 1 – ERD. State any assumptions necessary to support your design
There are 7 tables in total. Each of the tables have a primary key. Everyone’s account information will be put in the person table and customers/salary table based on their roles. The assumption here is that each staff member has a specific role (service Id) and job to do. They are also appointed in only one branch at a time. As for the salary table, every time the staff receive wages, it is recorded in the salary table, so it will act as a sort of ledger. Lastly, the appointments table is used to store and record all the appointments that are made by the customers.
Part 2 – Derive relational schema from ERD that represents entities and relationships. Identify primary and foreign keys
person(personId, username, password, firstName, lastName, sex, dateOfBirth)
customers(custId, personId, membership) Foreign Key personId References person(personId) On Delete No Action On Update Cascade
services(serviceId, serviceName, time)
salary(salaryID, date, amount, staffID) Foreign Key staffsID References staffs(staffsID) On Delete No Action On Update Cascade
appointments(appointmentId, custId, staffId, serviceId, date, startTime, endTime, progress) Foreign Key custId, staffId, serviceId References customers(custId), staffs(staffIDd, services(serviceId) On Delete No Action On Update Cascade
branch(branchID, seatLimit, branchName, city)
staffs(staffIId, personId, position, branchId, serviceId) Foreign Key personId, branchId, serviceId References services(serviceId), branch(branchId), person(personId) On Delete No Action On Update Cascade
Part 3 – Use the technique of normalization to validate the structure of your relational schema. Demonstrate that each of your relations is in third normal form
For a database to be in 3NF, it needs to follow all the rules of 1, 2, and 3 NF. We will explain while proving it in the paragraph below.
First Normal Form (1NF)
- It should only have single(atomic) valued attributes/columns.
- Values stored in a column should be of the same domain
- All the columns in a table should have unique names.
- And the order in which data is stored, does not matter.
In our table, one cell only contains one value, so it passes the first rule. All the values in one column all contain ONLY the information based on the column name, so it also passes the second rule. As for the third rule, all the columns in the table (as seen in ERD) have different names. Lastly, the order in which the data is stored does not matter because one row represents one individual. This shows that our tables follow the first NF.
Second Normal Form (2NF)
- It should be in the First Normal form.
- And, it should not have Partial Dependency.
In our table, none of our tables have partial dependency as information that is kept on one table isn’t duplicated in another. For example, in the staffs table, we only include branchId, serviceId, and personId as foreign keys. By not putting the branchName or the serviceName, we already refrain from having partial dependency. But for the position data, as there are only 2 positions available, we store it immediately in the staffs table and not in a separate table. This applies to all the other tables as well and as a result, our table follows the second NF.
Third Normal Form (3NF)
- It is in the Second Normal form.
- And, it doesn’t have Transitive Dependency.
In our table, none of our tables have transitive dependency. It is when a non-prime attribute depends on other non-prime attributes rather than depending upon the prime attributes or primary key. For example, in the appointments table. We don’t store the service time although it might need to be displayed in the app. Instead, we just store the service id as the foreign key. If the time is needed, it can be accessed from the foreign key itself.
Part 4 – User manual (introduce, describe functionality, demonstrate how to use)
Hello everybody and welcome to our web app, Sigma Salon! Sigma Salon is a web app designed to manage a pseudo salon database. The database contains 7 tables in order to store data on customers, staff, services, appointments, and more. The application is created in Python using the Flask module and MySQL as the DBMS.
Functionalities
Admin
If you’re an admin (have an admin account), you will be able to access all these tabs.
Add staff – To add staff, the admin will have to input username, password, first name, last name, sex, birthdate, position, branchId, and serviceId. There are validations applied to the text boxes above.
Add salary – To add salary, the admin will have to input staffId and amounts. There are validations applied to the text boxes above.
Add service – To add service, the admin will have to input the service name and time in minutes. There are validations applied to the text boxes above.
Add branch – To add a branch, the admin will have to input the branch name and city. There are validations applied to the text boxes above.
Free query – In the free query tab, there’s just an available text box. And a submit button. The admin can just input any query they want and the output will be in a list.
View appointments – In this tab, it will show a table of all the appointments with the joins, so that it’s easier to visualize the table. Because we included a lot of foreign key and numbers don’t mean anything here.
View all tables – In this tab it will show all the tables, with some joins.
Staffs
If you’re a staff member and you login using your staff account, you will be able to manage appointments.
Manage appointments – In this tab, they can see the not done tasks and if that appointment is done they can type in the text box and submit, then the database will be updated.
Customer
If you’re a customer, you have to first register, then login to access and book using the appointment tab.
Register – In the register form, user have to enter their details and it will be inserted into the database for authentication.
Login – In the login form, users have to enter username and password and it will be checked with the available data. If matched, it will continue to the customer screen.
Book appointments (part 1) – In the book appointment tab, they will need to choose a branch name and service name. Then they will be searched up in the database.
Book appointments (part 2) – Then , available staff will be shown accordingly. The user will have to input the staffid.
Book appointments (part 3) – Lastly, they will have to input the desired date and time and make sure that the schedule doesn’t clash with the already booked time.
