Intro to Database Systems Lectures 7-8 PDF

Introduction to Database Systems  Overview of Database Design Process ▸Database design process contains Two main activities: 1. Database Design ▹The focus in this presentation will be on the database design. Specifically, to design the conceptual schema for a dat...

Introduction to Database Systems  Overview of Database Design Process ▸Database design process contains Two main activities: 1. Database Design ▹The focus in this presentation will be on the database design. Specifically, to design the conceptual schema for a database application. 2. Applications Design ▹Applications design focuses on the programs and interfaces that access the database. Generally considered part of software 4 engineering.  Database Design Application Design Figure 1. A simplified diagram to illustrate the main phases of database design 3  Using High-Level Conceptual Data Models ▸Using High-Level Conceptual Data Models for Database Design ▹Requirements Collection and Analysis Task: Database designers interview prospective database users to understand and document data requirements Deliverables: Data requirements and Functional requirements of the application 4  Using High-Level Conceptual Data Models ▹Conceptual Design Task: conceptual model design including the description of data requirements. Includes detailed descriptions of the entity types, relationships, and constraints. Transformed from high-level data model into implementation data model. Deliverables: Conceptual schema (Conceptual model) e.g. ER- Model. 5  Using High-Level Conceptual Data Models ▹Logical Design or Data Model Mapping  Task: Maps conceptual model to the implemented logical model of target DBMS.  Deliverables: Logical schema )Logical model) e.g. Relational Schema.  Remarks: What we need to do(focus) on in this phase: Integrity, Constraints, and Normalization. 6  Using High-Level Conceptual Data Models ▹Physical Design Phase  Task: Determines how a database is implemented in a DBMS. Internal storage structures, file organizations, indexes, access paths, and physical design parameters for the database files specified.  Deliverables: Internal Schema (Physical model) e.g. File organization, Indexes.  Remarks: What we need to do(focus) on in this phase: Denormalization, Indexing, Derived data, Storage structure, Partitioning, View, Security, Query Optimization, Backup, Recovery, Concurrency control, Access control. 7  Data Modeling ▸Data modeling is the process of creating a model for the data to be stored in a specific format in a database (as explained in the previous lecture(. ▸Data models identify what data is needed and how it should be organized to enable the creation of a database that will be used to develop an app. They also ensure the quality of data via naming conventions and default values. ▸Data models can serve a variety of purposes, from high-level conceptual and logical models to physical data models (PDMs(. 8 We’ll briefly revise the types of data models in the next slide.  Data Models ▸There are three kinds of data models whose abstraction level are from high to low: 1. Physical model 2. Logical model [e.g. Relational-model] 3. Conceptual model [e.g. ER-model] Figure 2. Types of data models 11  Data Models 1. Conceptual Model ▹A data model that is independent of all implementation details like target DBMS, storage structure, security, performance issue. Example: ER model. 2. Logical Model ▹A logical data model describes the data in as much detail as possible, without regard to how they will be physical implemented in the DBMS. Examples: By ANSI -> Hierarchical Model, Network Model, Relational Model and Object- Oriented Model. 3. Physical Model ▹Data model that involved data management technology. 12  Example COMPANY Database ▸Requirements of the Company )oversimplified for illustrative Purposes( ▸The company is organized into DEPARTMENTs. Each department has a name, number and an employee who manages the department. We keep track of the start date of the department manager. ▹Each department controls several PROJECTs. Each project has a name, number and is located at a single location. 11  Example COMPANY Database ▸We store each EMPLOYEE’s social security number, address, salary, sex, and birthdate. Each employee works for one department but may work on several projects. We keep track of the number of hours per week that an employee currently works on each project. We also keep track of the direct supervisor of each employee. ▸Each employee may have a number of DEPENDENTs. For each dependent, we keep track of their name, sex, birthdate, and relationship to employee. 12  Data Modeling Techniques ▸Data models’ creation implies using specific data modeling techniques. ▸These are the two major data modeling techniques: 1. Entity Relationship Diagram (ERD( 2. UML (Unified Modeling Language( 3. Data Dictionary 13  UML (Unified Modeling Language) ▸UML (Unified Modeling Language) is a standardized family of notations for modeling and design of information systems. ▸UML is a generic data modeling language that standardizes the data, enabling the design of a system. ▸It comprises of several different diagrams representing different aspect of the system, and one of them being a Class Diagram that can be used for data modeling. ▹Class diagrams are equivalent of ERDs in relational world and are mostly used to design classes in object-oriented programming languages (such as Java or C # ). 14  UML (Unified Modeling Language) ▸In class diagrams architects define: ▹Classes (equivalent of entity in relational world), Attributes of a class (same as in an ERD) including data type, Methods associated to specific class, representing its behavior. ▹Relationships grouped into two categories: 1. Relationships between objects (instances of Classes) differentiated into Dependency, Association, Aggregation and Composition (equivalent to relationships in an ERD), and 2. Relationships between classes of two kinds Generalization/Inheritance and Realization/Implementation (this has no equivalent in relational world.) ▸You can use class diagrams to design a tabular data (such as in RDBMS), but were designed and are used mostly for object-oriented programs (such as Java or C#). 15  UML (Unified Modeling Language) 18 Figure 3. Example of UML Class Diagram  Data Dictionary ▸Data Dictionary is an inventory of data sets/tables with the list of their attributes/columns. ▸Techniques mentioned above were visual and were based on diagrams, and data dictionaries are a tabular definition/representation of data assets. ▸Data dictionary is suitable as detailed specification of data assets and can be supplemented with ER diagrams, as both serve slightly different purpose. 17  Data Dictionary ▸Core data dictionary elements: 1. List of data sets/tables, 2. List of attributes/columns of each table with data type. ▸Optional data dictionary elements: 1. Item descriptions, 2. Relationships between tables/columns, 3. Additional constraints, such as uniqueness, default values, value constraints or calculated columns. 18  Data Dictionary 21 Figure 4. Example of Data Dictionary  Entity Relationship Diagram (ERD( ▸Entity Relationship (ER) modeling is a best practice for producing well- designed databases. ▹It depicts the structure of a relational databases allowing you to understand the data and how it shares information. ▸A high-level conceptual data model diagram which provides a visual representation of the data and how it is interconnected. ▸ERDs work well if you want to design a relational (classic) database. Basically, any kind of tabular data. ▹ERD work well for visualization of database schemas and communication of top-level view of data. 20  Entity Relationship (ER) Modeling ▸The main concepts of an ER data model are: Entities – objects representing things from the real world. An entity is a collection of items having a common set of attributes, and each item has a combination of attribute values. Attributes – characteristics of properties of the entities. Relationships – how entities are related one to another; how they share information in the DB. 21  ER Model Concepts - Entities ▸Entities are specific objects or things in the mini-world that are represented in the database. ▹For example the EMPLOYEE John Smith, the Research DEPARTMENT, the ProductX PROJECT. ▸An entity may be: ▹weak entity: is an entity that is existence-dependent on some other entity ▹regular entity: is an entity that is not weak 22  ER Model Concepts - Entities ▸Each entity type is shown as a rectangle, labeled with the name of the entity type. ▸For a weak entity type the rectangle is doubled 23  ER Model Concepts - Attributes ▸Attributes are properties used to describe an entity. ▹For example, an EMPLOYEE entity may have a Name, SSN, Address, Sex, Birthdate. ▸A Specific Entity will have a Value for each of its attributes. ▹For example, a specific employee entity may have Name='John Smith', SSN='123456789', Address ='731, Fondren, Houston, TX', Sex='M', Birthdate='09-JAN-‘55 ▸Each attribute has a Value Set (or data type) associated with it. ▹E.g., integer, string, subrange, enumerated type… , 24  ER Model Concepts - Attributes Figure 5. Types of Attributes 25  ER Model Concepts - Attributes ▸Simple ▹Each entity has a single atomic value for the attribute. ▹For example, Age, Class, or Sex. ▸Single-valued ▹Single valued attributes are those attributes which can take only one value for a given entity from an entity set. ▹For example, Age, Class, or Sex. 26  ER Model Concepts - Attributes ▸Composite ▹The attribute may be composed of several components. ▹For example, Address (Apt#, House#, Street, City, State, ZipCode, Country), or Name (FirstName, MiddleName, LastName(. 27  ER Model Concepts - Attributes ▸Multi-valued ▹An entity may have multiple values for that attribute. ▹For example, Color of a CAR, MobNo, Email, or PreviousDegrees of a STUDENT. ▹Denoted as {Color}, {Mobno}, {Email), or {PreviousDegrees}. 28  ER Model Concepts - Attributes ▸Derived ▹Are those attributes which can be derived from other attribute(s( ▹For example, the attribute Age is a derived attribute as it can be derived from the attribute DOB (Date of Birth( 29