assets/: Contains images and documentation files.CreateScript.sql: SQL script for creating the database schema.InsertScript.sql: SQL script for inserting initial data into the database.Queries.sql: SQL script containing various queries to interact with the database.README.md: The main README file for the project.
- Database: PostgreSQL
- Languages: SQL, PLpgSQL
- Tools: DBeaver, pgAdmin
- Concepts: Relational Database Management, Data Integrity, Entity-Relationship Modeling
A year ago, on January 1, 2021, a chain of iFix service centers opened in England, focusing primarily on repairing devices from different manufacturers and selling various accessories for gadgets.
All branches of the service centers maintain personal databases. All customers and employees receive their own identification number upon registration, where they indicate their first name, last name, phone number, and can leave their email as an additional way of communicating with the service center. Employees are recorded with their working position, salary, and date of employment in the database. Each branch has several technicians (from 2 to 5), managed by one manager.
All customers of the service center bring their gadgets for repair (at least 1) and can buy accessories for their devices (as many as they want). Visitors' gadgets are registered using their IMEI numbers (6-9 digits), which are unique for all devices worldwide, along with the manufacturer, model of the device, and year of production (if necessary). The types of breakdowns of the brought devices and the payment method of each client are entered into the database. You can pay by card, with bank transfer, or cash. Each visitor can be served by several employees of the branch.
In addition to people and branches, their addresses are stored in the database. Each address has its own unique code, building number, street, district, and postal code. Since a considerable number of branches have opened in the country, the address is also associated with the city where the person lives or where the branch is located.
-
Employee β Person β Address β Branch:
- It is more convenient and rational to store the addresses of customers and branches in one table in the database, as it is necessary for the service center's operations. The result of this loop is that customers can live at the same address where a branch of the service center is located. More than one person can live at the same address, but the addresses of customers and branches do not overlap, which is described in the integrity constraint.
-
Employee β Employee:
- Only 1 manager or from 2 to 5 technicians can be employed in a branch. The technicians are led by a manager who is the main individual in each branch. Based on this loop, the manager can subordinate/lead himself, and this case is constrained by the integrity restriction.
-
Person β Service β Employee:
- As mentioned in the first loop, it is more rational to store data about all people (both customers and employees) in one database table. The result of this loop is that the branch employees can serve themselves, but it is not a problem since none of the employees stop working simultaneously, and the employee pays for the service like all customers.
