1 / 19

Database Modelling

Database Modelling. Dr. Awad Khalil Computer Science Department AUC. Content. Database Structure The Three-Layer Architecture Data Models Classification of DBMSs. Database Structure.

chelsea-hamilton
Download Presentation

Database Modelling

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Modelling Dr. Awad Khalil Computer Science Department AUC CSCI 253 -- Database Modelling

  2. Content • Database Structure • The Three-Layer Architecture • Data Models • Classification of DBMSs CSCI 253 -- Database Modelling

  3. Database Structure • A database structure is the description and definition of all basic structures such as simple conceptual files, datatypes, relationships, and constraints that should hold on the data. • In any data model it is important to distinguish between the description of the database (Schema) and the database itself (Instance). CSCI 253 -- Database Modelling

  4. Database Schema • The description of a database is called the database schema (or the meta-data). • A database schema is specified during database design and is not expected to change frequently. CSCI 253 -- Database Modelling

  5. CREATE TABLE EMPLOYEE (FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN SSN_TYPE NOT NULL, BDATE DATE ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSN SSN_TYPE, DNO INT NOT NULL, PRIMARY KEY (SSN), FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)); CREATE TABLE DEPARTMENT (DNAME VARCHAR(15) NOT NULL, DNUMBER INT, NOT NULL, MGRSSN SSN_TYPE NOT NULL, MGRSTARTDATE DATE PRIMARY KEY (DNUMBER), UNIQUE (DNAME) FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN)); CREATE TABLE DEPT_LOCATIONS (DNUMBER INT NOT NULL, DLOCATION VARCHAR(15) NOT NULL, PRIMARY KEY (DNUMBER, DLOCATION), FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT(DNUMBER)); CREATE TABLE PROJECT (PNAME VARCHAR(15) NOT NULL, PNUMBER INT NOT NULL, PLOCATION VARCHAR(15) DNUM INT NOT NULL, PRIMARY KEY (PNUMBER), UNIQUE (PNAME) FOREIGN KEY (DNUM) REFERENCES DEPARTMENT(DNUMBER)); CREATE TABLE WORKS_ON (ESSN SSN_TYPE NOT NULL, PNO INT NOT NULL, HOURS DECIMAL(3,1) NOT NULL, PRIMARY KEY (ESSN, PNO), FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY (PNO) REFERENCES PROJECT(PNUMBER)); CREATE TABLE DEPENDENT (ESSN SSN_TYPE NOT NULL, DEPENDENT_NAME VARCHAR(15) NOT NULL, SEX CHAR,   BDATE DATE,   RELATIONSHIP VARCHAR(8) PRIMARY KEY (ESSN, DEPENDENT_NAME), FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN)); A Database Schema in SQL CSCI 253 -- Database Modelling

  6. Database State (Instance) • The data in the database at a particular moment of time is called the database state (or instance). CSCI 253 -- Database Modelling

  7. The Three-Layer Architecture CSCI 253 -- Database Modelling

  8. Data Models • A data model is a set of concepts that can be used to describe a database structure. CSCI 253 -- Database Modelling

  9. DBMS Generations CSCI 253 -- Database Modelling

  10. Data Independence • Data independence is the capacity to change the schema at one level of a database system without having to change the schema at the next higher level. defined: • Logical data independence is the capacity to change the conceptual schema without having to change external schemas or Logical data application programs. • Physical data independence is the capacity to change the internal schema without having to change the conceptual (or external) schemas. CSCI 253 -- Database Modelling

  11. Classification of DBMSs • Classification according to Data Model CSCI 253 -- Database Modelling

  12. The Relational Data Model • The relationaldata model represents the database as a collection of tables, where each table can be stored as separate file. • Examples of commercial relational DBMSs: • DB2 from IBM • ORACLE from Oracle Corporation • Informix from Informix • SyBase from OpenSoft • SQL Server from Microsoft • MS-ACCESS from Microsoft CSCI 253 -- Database Modelling

  13. An Example of a Relational Database CSCI 253 -- Database Modelling

  14. The Network Data model • The networkdata model represents data as a record types. An example of a network model is known as the CODASYL DBTG model. CSCI 253 -- Database Modelling

  15. The Hierarchical Data model • The hierarchical data model represents data as hierarchical tree structure. Each hierarchical represents a number of related records. CSCI 253 -- Database Modelling

  16. The object-oriented data model defines a database in terms of objects, their properties, and their operations. Objects with the same structure and behavior belong to a class, and classes are organized into hierarchies or a cyclic graphs. The operations of each class are specified in terms of predefined procedures called methods. Experimental OO prototypes The ORION system developed at MCC, The OpenOODB system at Texas Instruments, The IRIS system developed at HP laboratories, The ODE system at ATT Bell Labs, and The ENCORE/ObServer project at Brown University. Commercially available OO systems GEM-STONE/OPAL of SerioLogic, ONTOS of Ontologic, Objectivity of Objectivity Inc., Versant of Versant Technologies, ObjectStore of Object Design, and, O2 of O2 Technology. The Object-Oriented Data model CSCI 253 -- Database Modelling

  17. Classification according to Number of Users Single user systems support only one user at a time and are mostly used with personal computers. Multiuser systems, which include the majority of DBMSs, support many users concurrently. Classification of DBMSs CSCI 253 -- Database Modelling

  18. Classification according to Number of Sites Centralized DBMS where the data is stored at a single computer site. Most DBMSs are centralized. A centralized DBMS can support multiple users, but the DBMS and the database themselves reside totally at a single computer site. Distributed DBMS (DDBMS) can have the actual database and DBMS software distributed over many sites, connected by a computer network. Many DDBMSs use a client-server architecture. Classification of DBMSs CSCI 253 -- Database Modelling

  19. Thank you CSCI 253 -- Database Modelling

More Related