COMP2350 Database Design PDF

External Schema: User-speci c views, Simpli es data access, Does not impact database storage Conceptual Schema: High-level database structure, Focuses on relationships and organization, Independent of both user views and storage Logical (Internal) Schema: Physical data storage, Optimization and pe...

External Schema: User-speci c views, Simpli es data access, Does not impact database storage Conceptual Schema: High-level database structure, Focuses on relationships and organization, Independent of both user views and storage Logical (Internal) Schema: Physical data storage, Optimization and performance focus, Hidden from users Relationships: External schema provides different user views, mapped to the conceptual schema. Conceptual schema designs the data model, implemented physically by the logical schema. fi fi 1 Strong Entity Create a table for each strong entity. Include all attributes and define the primary key (PK). Example: Employee(EmployeeID(PK), Name, Age). 2 Weak Entity Create a table for each weak entity. Include the primary key of the strong entity as a foreign key (FK). Use a composite primary key (PK) with its own attributes. Example: Dependent(DependentID, EmployeeID(PK, FK), Name). 3 1:1 Relationship Borrow the PK from one entity as an FK in the other entity. If optional, choose the entity based on design judgment. Example: Passport(PassportID(PK, FK), PersonID(PK, FK)). 4 1:M Relationship Add the primary key (PK) of the "one" side as a foreign key (FK) in the "many" side entity. Example: Employee(EmployeeID(PK), DepartmentID(FK), Name). 5 M:M Relationship Create a junction table with foreign keys (FK) from both entities. Use a composite primary key (PK) formed by these foreign keys. Example: Enrollment(StudentID(PK, FK), CourseID(PK, FK), EnrollmentDate). 6 Associative Entity Create a junction table with the foreign keys (FK) from both related entities. Add any relationship-specific attributes. Example: OrderItem(OrderID(PK, FK), ProductID(PK, FK), Quantity, Price). 7 Multi-Valued Attribute Create a new table for the multivalued attribute. Include the foreign key (FK) from the related entity. Define the new attribute as a part of the primary key (PK). Example: EmployeeSkills(EmployeeID(FK, PK), SkillName(PK)). 8 Inhe A General Create a table for each subtype. ritan Borrow the primary key (PK) from the supertype as a foreign key (FK) in each subtype table. ce Add the specific attributes related to each subtype. Example: Product(ProductID, Name, Description) Subtypes:FoodItem(ProductID(PK, FK), ExpiryDate) B Total Merge all attributes from the supertype into each subtype table. Relationship Each subtype will include attributes from the supertype with its own specific attributes. Restriction Each entity can be associated with multiple subtypes. with Overlap Example: Product is removed, and the following are created: FoodItem(ProductID(PK), Name, Description, ExpiryDate) C Partial Combine the supertype and subtype into one table. Disjoint Add a "Type" column to indicate which subtype (if any) the entity belongs to. Keep all the attributes of both the supertype and the specific subtype in a single table. Only one subtype can be selected for each instance. Example: Product(ProductID, Name, Description, Type, ExpiryDate, IsCold, Supplier, Weight) d Partial Combine the supertype and subtype into one table. Overlap Add a flag column for each subtype to indicate whether the entity belongs to that subtype. An entity to belong to multiple subtypes simultaneously. Example: Product(ProductID, Name, Description, IsFood, IsDrink, IsSundry, ExpiryDate, IsCold, Supplier, Weight) -- Creating a database CREATE DATABASE company_db; -- Using the created database USE company_db; -- Creating the employees table CREATE TABLE employees ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, -- Auto-incrementing ID name VARCHAR(50) NOT NULL, -- Employee name position VARCHAR(50), -- Employee position salary DECIMAL(10, 2) NOT NULL, -- Employee salary department_id INT UNSIGNED, -- Foreign key reference to department PRIMARY KEY (id) -- Setting the primary key ); -- Creating the departments table CREATE TABLE departments ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, -- Auto-incrementing ID department_name VARCHAR(50) NOT NULL, -- Department name PRIMARY KEY (id) -- Setting the primary key ); -- Inserting data into departments INSERT INTO departments (department_name) VALUES ('Sales'); INSERT INTO departments (department_name) VALUES ('Marketing'); INSERT INTO departments (department_name) VALUES ('HR'); -- Inserting data into employees INSERT INTO employees (name, position, salary, department_id) VALUES ('Alice', 'Manager', 75000, 1), ('Bob', 'Sales Rep', 50000, 1), ('Charlie', 'Marketing Lead', 60000, 2), ('Diana', 'HR Coordinator', 45000, 3), ('Ethan', 'Sales Rep', 55000, 1); -- Selecting all employees SELECT * FROM employees; -- Updating an employee's salary UPDATE employees SET salary = salary + 5000 WHERE name = 'Bob'; -- Deleting an employee record DELETE FROM employees WHERE name = 'Diana'; -- Selecting employees with a specific condition SELECT name, position, salary FROM employees WHERE salary > 55000; -- Filtering employees with salary greater than 55000 -- Counting the number of employees in each department SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id; -- Grouping by department -- Using LIKE to find employees with a specific pattern in their names SELECT name FROM employees WHERE name LIKE 'A%'; -- Employees whose names start with 'A'z -- Using JOINs to get employee and department information -- INNER JOIN: Get employees and their departments SELECT e.name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.id; -- LEFT JOIN: Get all employees and their departments, even if some employees don't have a department SELECT e.name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id; -- RIGHT JOIN: Get all departments and their employees, even if some departments don't have employees SELECT e.name, d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.id; -- FULL OUTER JOIN: Get all employees and all departments SELECT e.name, d.department_name FROM employees e FULL OUTER JOIN departments d ON e.department_id = d.id; -- CROSS JOIN: Get a Cartesian product of employees and departments SELECT e.name, d.department_name FROM employees e CROSS JOIN departments d; -- SEMI JOIN: Get employees who belong to a department SELECT e.name FROM employees e WHERE EXISTS ( SELECT 1 FROM departments d WHERE e.department_id = d.id ); -- ANTI JOIN: Get employees who do not belong to any department SELECT e.name FROM employees e WHERE NOT EXISTS ( SELECT 1 FROM departments d WHERE e.department_id = d.id ); -- Ordering employees by salary SELECT name, salary FROM employees ORDER BY salary DESC; -- Sorting by salary in descending order -- Pagination with LIMIT and OFFSET SELECT name, position, salary FROM employees ORDER BY salary DESC LIMIT 2 OFFSET 1; -- Get the second employee in the sorted result Generalized SQL Pattern for Division: To implement the division operator in SQL, follow this general pattern: SELECT A.attribute FROM A JOIN Relation ON A.attribute = Relation.attribute GROUP BY A.attribute HAVING COUNT(DISTINCT Relation.attribute) = (SELECT COUNT(*) FROM B); A.attribute: The attribute of the entity you are dividing. Relation: The relationship between entities in A and B. B: The set you want to divide by. HAVING COUNT(DISTINCT...) = (SELECT COUNT(*) FROM B): Ensures the entity in A is related to all entities in B.