Session 3 - Database Design Process .pdf

Module Code: CIT111 Module Name : Fundamentals of Database Management Systems Session 03 Title : Database Design Process Date : 31st July 2024  Resource Person Mr. Dhanushka Kumanayake Email: [email protected] LinkedIn Profile -...

Module Code: CIT111 Module Name : Fundamentals of Database Management Systems Session 03 Title : Database Design Process Date : 31st July 2024  Resource Person Mr. Dhanushka Kumanayake Email: [email protected] LinkedIn Profile - www.linkedin.com/in/dhanushka-kumanayake 2 2024 © SLTC Research University Objectives ❖ Learn about the Entity-Relationship (ER) model ❖ Learn about advanced ER modeling ❖ Map Conceptual model into relational schema 2024 © SLTC Research University 3 Database Design in Bird Eye Requirements Collection and Analysis Conceptual Design Logical Design Physical Design 2024 © SLTC Research University 4 Requirements Collection and Analysis ❖ Interviews ❖ Surveys and Questionnaires ❖ Observation ❖ Document Reviews 2024 © SLTC Research University 5 Conceptual Design This is high level description of the structure of a database E.g. ER diagram Concise description of the data requirements of the users and includes detailed descriptions of the data, relationships and constraints. 2024 © SLTC Research University 6 Logical Design This is the process of mapping the database structure developed in the previous phase to a particular database model. E.g. map ER model to relational 2024 © SLTC Research University 7 Physical Design This is the process of defining structure that enables the database to be queried in an efficient manner. E.g. Schema Designing, indexing, and data partitioning 2024 © SLTC Research University 8 ER diagram An Entity-Relationship (ER) diagram is a visual representation of the entities, relationships, and attributes within a database system. It is used in database design to illustrate the logical structure of the data and how different entities are connected to each other Note: Chen modeling for ER diagraming 2024 © SLTC Research University 9 Symbols for ER diagram 2024 © SLTC Research University 10  Symbols for ER diagram Con… ❖ One to one Relationship ❖ One to Many Relationship ❖ Many to Many Relationship 2024 © SLTC Research University 11 Case Study1: Employee Management ❖ A company is divided into departments. Each department has a unique number and an employee responsible for managing it. We also keep a record of the start date when the employee began overseeing the department. Additionally, a department can have multiple locations. ❖ A department oversees several projects, each of which has a name, a unique number, and a single location. ❖ We maintain records of each employee's name, national ID number, address, salary, birth date, and gender. We use unique number to identify employee. Each employee is assigned to a single department but may work on multiple projects, which do not necessarily belong to the same department. We also track the number of hours per week that an employee works on each project and record the direct supervisor for each employee. 2024 © SLTC Research University 12 Case Study1: Employee Management Con… ❖ We maintain records of each employee's dependents for insurance purposes, including each dependent's name, gender, birth date, and relationship to the employee. 2024 © SLTC Research University 13 Conceptual Design: Case study 1 Entities ❖ Department ❖ Employee ❖ Project ❖ Dependent 2024 © SLTC Research University 14 Conceptual Design: Case study 1 Con… Relationships ❖ A Department has Many Employees ❖ An Employee works for A Department 2024 © SLTC Research University 15 Conceptual Design: Case study 1 Con… Relationships ❖ A Department has A Manager ❖ An Employee manage A Department 2024 © SLTC Research University 16 Conceptual Design: Case study 1 Con… Relationships ❖ A Department controls Many Projects ❖ A Project controlled by A Department 2024 © SLTC Research University 17 Conceptual Design: Case study 1 Con… Relationships ❖ An Employee works on Many Projects ❖ A Project has Many Employees 2024 © SLTC Research University 18 Conceptual Design: Case study 1 Con… Relationships ❖ An Employee supervised by An Employee ❖ An Employee supervise Many Employees 2024 © SLTC Research University 19 Conceptual Design: Case study 1 Con… Relationships ❖ An Employee has Many Dependents ❖ A Dependent belongs to An Employee 2024 © SLTC Research University 20 Conceptual Design: Case study 1 Con… Relationships ❖ A Department has Many Locations ❖ A Location has one department 2024 © SLTC Research University 21 Conceptual Design: Case study 1 Con… Attributes Department Employee ❖ DepartmentNumber (PK) ❖ EmpNo (PK) ❖ Name ❖ NationalID ❖ StartDate (for the manager) ❖ Name ❖ Location (multiple locations ❖ Address allowed) ❖ Salary ❖ BirthDate ❖ Gender 2024 © SLTC Research University 22 Conceptual Design: Case study 1 Con… Attributes Project Dependent ❖ ProjectNumber (PK) ❖ DependentID (PK) ❖ Name ❖ Name ❖ Location ❖ Gender ❖ BirthDate ❖ Relationship 2024 © SLTC Research University 23 Relationship Types ❖ Unary Relationship ❖ Binary Relationship ❖ Ternary Relationship 2024 © SLTC Research University 24 Introduction to Advanced ER Modeling Advanced ER Modeling involves extending the basic Entity- Relationship (ER) model to handle more complex data relationships and constraints. Understanding advanced ER modeling is crucial for designing robust, scalable, and efficient databases that accurately reflect complex real-world scenarios. 2024 © SLTC Research University 25 Extended ER (EER) Model The Extended ER (EER) model enhances the traditional ER model by incorporating additional semantic constructs. ❖ Key Components: Specialization, Generalization. ❖ Benefits: The EER model allows for a more nuanced and detailed representation of data, facilitating better database design. 2024 © SLTC Research University 26 Specialization and Generalization ❖ Specialization: The process of defining a set of subtypes for a supertype based on some distinguishing characteristic. ❖ Generalization: The process of defining a more general entity type from a set of subtypes. Examples ❖ Specialization: Employees specialized into Managers, Engineers, and Technicians. ❖ Generalization: Different types of vehicles generalized into a Vehicle supertype. 2024 © SLTC Research University 27 Supertypes and Subtypes ❖ Supertypes: A generic entity type that has a relationship with one or more subtypes. ❖ Subtypes: A more specific entity type that inherits attributes and relationships from a supertype. Usage: Supertypes and subtypes are used to model hierarchical relationships and shared attributes among entities. 2024 © SLTC Research University 28 Supertypes & Subtypes E.g. Supertype: Employee Subtypes: Manager,Engineer,Technician 2024 © SLTC Research University 29  Specialization E.g. Supertype: Employee Subtypes:Driver, Developer Specialization is also called as ” Top-Down approch”. 2024 © SLTC Research University 30 Generalization E.g. Subtypes: Faculty, Student Generalized Entity: Person Generalization is also called as ‘ Bottom-up approach”. 2024 © SLTC Research University 31  Case Study II In an educational institute, there are multiple departments, each with students assigned to them. Every department is identified by a unique department number and also has a name, location, phone number, and a head professor. Professors are identified by a unique employee ID, and their details include name and phone number. For students, we record their name, unique roll number, gender, date of birth, age, and one or more email addresses. Students have a local address, which includes the hostel name and room number, and a home address, which includes house number, street, city, and PIN. All students are assumed to reside in hostels. A course taught during a semester is referred to as a section. There can be multiple sections of the same course in a semester, identified by section numbers. Each section is instructed by a different professor and has its own schedule and meeting room. Students enroll in multiple sections each semester. Each course has a name, credit count, and is offered by a specific department. Some courses have prerequisites, meaning they require the completion of other courses before enrollment. Professors also conduct research projects, which are funded by agencies and have defined start and end dates and allocated budgets. Multiple professors can collaborate on a project, and a professor may work on several projects simultaneously. Each project has a unique project ID. 2024 © SLTC Research University 32 Thank You! 2024 © SLTC Research University 33