Database Fundamentals (CS2231) Lecture 2 PDF

Database Fundamentals (CS2231) Lecture 2 Database Models/ Database Architecture 1 st Trimester 1446 1 Outline  What is a Database/Data Model  Basic Terminologies  Types of Database Models  Hierarchical Database Model  Network Database Model ...

Database Fundamentals (CS2231) Lecture 2 Database Models/ Database Architecture 1 st Trimester 1446 1 Outline  What is a Database/Data Model  Basic Terminologies  Types of Database Models  Hierarchical Database Model  Network Database Model  Relational Database Model  Object-Oriented Database Model Database Schema vs. Database State Three-Level Architecture Data Independence 2 Database Models What is a Database Model/ Data Model ?  A Database model is a type of data model that determines the logical structure of a database and fundamentally determines in which manner data can be stored, organized, and manipulated.  The most popular example of a database model is the relational model, which uses a table-based format. 3 Database Models Basic Terminologies ◦ Entity: Anything (person, place, event, object, thing…) tangible/non-tangible about which data are to be captured and stored. ◦ Attribute: Characteristics of an entity. ◦ Relationship: Association among entities. 1:M, M:N and 1:1 ◦ Constraints: Restrictions placed on the data. 4 Database Models Business Rules: ◦ Brief, precise, and unambiguous description of a policy, procedure, and principle of an organization. ◦ Business rules help identifying entities, attributes, relationships and constraints. ◦ Noun in business rules form entity in data model ◦ Verb associating nouns creates relationships among the entities. 5 Database Models Hierarchical Database Model: ◦ Organizes data in a tree structure. ◦ It contains nodes connected by branches. ◦ The top node is called the root. ◦ Each node has exactly one parent. ◦ One parent may have many children, thereby allowing 1:M relation. 6 Database Models Hierarchical Database Model: IS and CS courses CS Courses INFO Courses OOP DB IT skills 7 Problems in the Hierarchical Database Model While the hierarchical model is a suitable one for simple structures, it is easy to see how problems might arise, as each "child" can only have one "parent.“ Difficult to represent "many-to one" or "many-to-many“. Complex implementation. Difficult to manage. Lack of standards. 8 Advantages of the Hierarchical Database Model Conceptual simplicity. Database security. Data independence. Database integrity. Efficiency dealing with a large database. 9 Database Models Network Database Model: ◦ Consists of nodes and branches. ◦ Permitted the modeling of many-to-many relationships in data. ◦ Relationships are represented as links. ◦ Data are represented as collection of records. ◦ Each record is a collection of fields. 10 Network Database: Example INFO Courses INFO 605 INFO 620 001 Tom 001 Mary 11 Database Models Problems in the Network Database Model: Access to the database via API(Application Programming Interface) only, SQL is not supported. Each API can only access one single table (dataset), so joining is not possible. WHERE clause is not supported ORDER BY clause is not supported Making structural modifications to the database is very difficult in the network database model 12 Database Models Advantages of the Network Database Model Provide very efficient "High-speed" retrieval The network model can handle the one-to-many and many-to-many relationships Ease of data access 13 Database Models: Relational Database Model ◦ Data and relations between them are organized in tables. ◦ A table is a collection of records/tuples and each record in a table contains the same fields. ◦ Based on relational algebra. 14 Database Models: Relational Database Model ◦ Tables are called relations. ◦ Column/field values are atomic. ◦ Each row is unique. ◦ Column values are homogeneous and has a domain, i.e. set of possible values. ◦ The sequence of columns and rows is insignificant. ◦ Each column has a unique name. ◦ Certain fields may be designated as keys (indexing, speeding up query and joining with other tables). 15 Database Models: Relational Database Model record 16 Database Models Advantages of the Relational Database Model: Data can be shared. Provides backup and recovery. Cost of developing and maintaining is low. Restricting Unauthorized Access. Easier database design, implementation, management, and use. Query capability (SQL). Powerful database management system. Control redundancy. 17 Database Models Disadvantages of the Relational Database Model: Cost of DBMS and Hardware is high. Complexity. Size: the complexity makes the DBMS extremely large and need huge amount of storage space. 18 Database Models: Relational Database Model Entity Relationship Model (ERM): It is easier to examine database structure graphically than in SQL text. ◦ ERM facilitates graphical representation, called Entity Relation Diagram (ERD), to model database components i.e. entities and their relationships. ◦ An Entity is represented as a rectangle box, which is presented in capital and singular form. STUDENT ◦ An Entity can be compared as a table in relational data model. ◦ Each row in a relational table is an Entity instance. ◦ In an ER model, an Attribute name appears in an oval that has a line to the corresponding Entity box. STUDENT StudentGrade 19 Database Models: Relational Database Model Entity Relationship Model (ERM): ◦ The set of all possible values of an attribute is the Attribute domain. ◦ Attributes can be atomic or composite (multiple). First Last name name name StudentID primary STUDENT ◦ Attributes can be single-valued or multi-valued. ◦ An attribute or set of attributes that uniquely identifies a particular entity is a key. 20  E-R Diagram: called also Chen Model Relationship: Relationships describe associations between two Entities, shown as diamond. Degrees of relationships can be 1:M, M:N and 1:1. 21 Database Models: Relational Database Model Entity Relationship Model(ERM) last slide ◦ Types of relationship can be unary, binary, ternary, N-ary. PROFESSOR Recommends TEXTBOOK COURSE STUDENT help 22 Database Models: Object-Oriented Database ◦ Modeled both data and their relationships in a single structure (object). ◦ Modularity facilitated program reuse and construction of complex structures. ◦ Ability to handle complex data types (e.g. multimedia data). 23 Database Models: Object-Oriented Database Object-Oriented DataBase Model (OODBM) ◦ Maintains the advantages of the ER model but adds more features ◦ Object = entity + relationships (between & within entity) ◦ consists of attributes & methods ◦ attributes describe properties of an object ◦ methods are all relevant operations that can be performed on an object ◦ Class = collection of similar objects with shared attributes and methods ◦ Incorporates the notion of inheritance ◦ attributes and methods of a class are inherited by its descendent classes 24  OO DataBase Model Vs. E-R Model OODBM: - can accommodate relationships within the object - objects to be used as building blocks for autonomous structures many one 25  Database Models: Object-Oriented ‫امتداد للريالشونال داتا بيس‬ Database Advantages ◦ Semantic representation of data ◦ fuller and more meaningful description of data via object ◦ Modularity, reusability, inheritance ◦ Ability to handle ◦ complex data ◦ sophisticated information requirements 26 Database Models: Object-Oriented Database Disadvantages: ◦ Lack of standards ◦ No standard data access method ◦ Complex navigational data access ◦ Difficult to design and implement properly ◦ High system overhead ◦ Slow transactions 27 Outline What is a Database/Data Model  Basic Terminologies  Types of Database Models  Hierarchical Database Model  Network Database Model  Relational Database Model  Object-Oriented Database Model Database Schema vs. Database State Three-Level Architecture Data Independence 28 Database Schema vs. Database State Database Schema: o The description of a database. o Includes descriptions of the database structure, data types, and the constraints on the database. Schema Diagram: o An illustrative display of (most aspects of) a database schema. Schema Construct: o A component of the schema or an object within the schema, e.g., STUDENT, COURSE. 29 Database Schema vs. Database State Database State: Refers to the content of a database at a moment in time. Initial Database State: Refers to the database state when it is initially loaded into the system. Valid State: A state that satisfies the structure and constraints of the database 30 Database Schema vs. Database State Distinction: The database schema changes very infrequently. The database state changes every time the database is updated. 31 Example of a Database Schema 32 Example of a database state 33 Outline What is a Database/Data Model  Basic Terminologies  Types of Database Models Database Schema vs. Database State Three-Level Architecture  External Level  Conceptual  Internal Level  Data Independence 34 The Three-Level Architecture 35 Types of Modeling cont’ Each end user can see a subset of overall data Database administrator sees the global view of entire database Seen by DBMS 36 The Three-Level Architecture LEVEL Degree of Focus Independent of Abstraction External High End-user Hardware and Software Conceptual Global view of Hardware and data Software Internal Specific database Hardware model 37 Level 1 :External Level  The users’ view of the database. This level describes that part of the database that is relevant to each user.  Users may have different views of the same data e.g. date, time etc.  Virtual/calculated data: that is not actually stored in the database but is created when needed e.g. age, statistical data etc.  External schema evolves as user needs are modified over time 38 Level 2: Conceptual Level  A complete description of the information content of the database.  The entire information structure of the database, as seen by the DBA(Database administrator).  This level represents:  All entities, attributes, and their relationships.  Constraints on data.  Semantic information about the data meaning.  Security and integrity information. 39 Level 3: Internal Level  Covers the physical representation of the database to achieve optimal runtime performance and storage space utilization.  The internal level is concerned with:  Storage space allocation for data and indexes.  Record descriptions for storage (with stored sizes for data items).  Data compression and data encryption techniques. 40 Inter-Schema Mappings  Mappings among schema levels are needed to transform requests and data.  Programs refer to an external schema, and are mapped by the DBMS to the internal schema for execution.  Data extracted from the internal DBMS level is reformatted to match the user’s external view (e.g. formatting the results of an SQL query for display in a Web page) 41 The Three-Level Architecture 42 Inter-Schema Mappings  External/Conceptual mapping:  Specifies mapping between objects in the external view to those in the conceptual level.  Conceptual/Internal mapping:  Specifies mapping between objects in the conceptual level to those in the internal level. 43 Data Independence  Logical Data Independence:  The capacity to change the conceptual schema without having to change the external schemas and their associated application programs.  Physical Data Independence:  The capacity to change the internal schema without having to change the conceptual schema.  For example, the internal schema may be changed when certain file structures are reorganized, or new indexes are created to improve database performance 44 Data Independence  When a schema at a lower level is changed, only the mappings between this schema and higher level schemas need to be changed in a DBMS that fully supports data independence.  The higher-level schemas themselves are unchanged.  Hence, the application programs need not be changed since they refer to the external schemas. 45  Types of Changes Conceptual Level:  Adding a new file.  Adding a new field in a file.  Deleting an attribute.  etc. Internal Level:  Changing file organization.  Changing the Index implementation etc. e.g. hash, tree etc.  Changing storage medium. 46  Conclusion A Database model is a type of data model that determines the logical structure of a database. Types of Database Models, Hierarchical Database Model, Network Database Model, Relational Database Model, Object-Oriented Database Model. Entity Relationship Model (ERM ) facilitates graphical representation, called Entity Relation Diagram (ERD), to model database components ( entities and their relationships.) Database Schema: Defines the structure, is mostly static, and determines how data should be organized. While the Database State: Represents the actual data at any moment. Three-Level Schema Architecture: External Level, Conceptual Level, and Internal Level. Data Independence: Logical Data Independence, and Physical Data Independence. 47