DBMS Part 1 and 2 PDF

Chapter 3: Database Systems © 2015 Pearson Education Limited 2015 Examples of Database Applications  Purchases from the supermarket  Purchases using your credit card  Booking a holiday at the travel agents  Using the local library  Taking out insurance  Renting a video...

Chapter 3: Database Systems © 2015 Pearson Education Limited 2015 Examples of Database Applications  Purchases from the supermarket  Purchases using your credit card  Booking a holiday at the travel agents  Using the local library  Taking out insurance  Renting a video  Using the Internet  Studying at university Chapter 1 Definitions  Data: stored representations of meaningful objects and events or  Referred to facts concerning objects and events that could be recorded and stored on computer media  Structured: numbers, text, dates  Unstructured: images, video, documents  Information: data processed to increase knowledge in the person using the data  Metadata: data that describes the properties and context of user data Chapter 1 3 Definitions of Database  Def 1: Database is an organized collection of logically related data  Def 2: A database is a shared collection of logically related data that is stored to meet the requirements of different users of an organization  Def 3: A database is a self-describing collection of integrated records  Def 4: A database models a particular real world system in the computer in the form of data Chapter 1  What is a Database  Shared collection of logically related data (and a description of this data), designed to meet the information needs of an organization.  Databases are a method for storing data in a structured way on a computer, to make it easier to store, search, and work with that data.  Data is typically stored in tables in a database. You can visualize each table as having columns, or fields, which hold particular categories of data (e.g. first name), and rows, or records, which hold all of the information about a particular thing (e.g. a customer). Chapter 1 5  Figure 1-1a Data in Context Context helps users understand data Chapter 1  Graphical displays turn data into useful information that managers can use for decision making and interpretation Chapter 1  Descriptions of the properties or characteristics of the data, including data types, field sizes, allowable values, and data context Chapter 1  Before Databases: File Processing System  Commercial applications introduced File Processing System  A File Processing System, in computer science, is a method to read, write, modify, and store data in files  A collection of application programs that perform services for the end-users such as production of reports  Each program defines and manages its own data Chapter 1 9 File Processing Systems Library Examination Registration Library Examination Registration Applications Applications Applications Library Examination Registration Data Data Data Files Files Files Chapter 1 Program and Data Interdependence 10  File Processing Systems Library Examination Registration Reg_Number Reg_Number Reg_Number Name Name Name Father Name Address Father Name Books Issued Class Phone Fine Semester Address Grade Class Chapter 1 11 Disadvantages of File Processing  Program-Data Dependence  File structure is defined in the program code.  All programs maintain metadata for each file they use  Duplication of Data (Data Redundancy)  Different systems/programs have separate copies of the same data  Same data is held by different programs.  Wasted space and potentially different values and/or different formats for the same item.  Limited Data Sharing  No centralized control of data  Programs are written in different languages, and so cannot easily access each other’s files. Chapter 1 Disadvantages of File Processing  Lengthy Development Times  Programmers must design their own file formats  Excessive Program Maintenance  80% of of information systems budget  Vulnerable to Inconsistency  Change in one table need changes in corresponding tables as well otherwise data will be inconsistent Chapter 1 13  Problems with Data Dependency  Each application programmer must maintain their own data  Each application program needs to include code for the metadata of each file  Each application program must have its own processing routines for reading, inserting, updating and deleting data  Lack of coordination and central control  Non-standard file formats Chapter 1 Problems with Data Redundancy  Waste of space to have duplicate data  Causes more maintenance headaches  The biggest problem:  When data changes in one file, could cause inconsistencies (Vulnerable to Inconsistency)  Compromises data integrity (data reliability) Chapter 1 SOLUTION: The DATABASE Approach  Central repository of shared data  Data is managed by a controlling agent  Stored in a standardized, convenient form This requires a Database and Database Management System (DBMS) Chapter 1  Advantages of Database Approach Library Examination Registration Library Examination Registration Applications Applications Applications Database Management System - Data Sharing - Data Independence - Controlled Redundancy University - Better Data Integrity Students Database Chapter 1 17 Database Management System  A software system that is used to create, maintain, and provide controlled access to users of a database  (Database) application program: A computer program that interacts with database by issuing an appropriate request (SQL statement) to the DBMS Chapter 1 Database Management System DBMS manages data resources like an operating system manages hardware resources Chapter 1 Chapter 1 Chapter 1  Database Models Database model: A conceptual view of a database  Relational database model  Object-oriented database model Chapter 1 9-22 The Entity Relational Model ER is a model for identifying entities to be represented in the database and representation of how those entities are related. The ER data model specifies enterprise schema that represents the overall logical structure of a database graphically. Chapter 1 23 Chapter 1 24 Chapter 1 25 Chapter 1 26 Chapter 1 27 Chapter 1 28 Chapter 1 29 Chapter 1 30 Chapter 1 31 Chapter 1 32 Database Systems PART II Chapter 1 33 3. Entity-Relationship Model An ER model is the logical representation of data as objects and relationships among them. These objects are known as entities, and relationship is an association among these entities. Entity-Relationship Model Components ER diagram basically having three components: 1.Entities − It is a real-world thing which can be a person, place, or even a concept. For Example: Department, Admin, Courses, Teachers, Students, Building, etc are some of the entities of a School Management System. 2.Attributes − An entity which contains a real-world property called an attribute. For Example: The entity employee has the property like employee id, salary, age, etc. 3.Relationship − Relationship tells how two attributes are related. For Example: Employee works for a department. Chapter 1 34 Entity - Relationship Model A logical design method which emphasizes simplicity and readability. Basic objects of the model are: Entities Relationships Attributes Chapter 1 Entities Data objects detailed by the information in the database. Denoted by rectangles in the model. Employee Department Chapter 1 Tables Explained The schema of a table is the table name and its attributes: Product(PName, Price, Category, Manfacturer) A key is an attribute whose values are unique; we underline a key Product(PName, Price, Category, Manfacturer) Chapter 1 Attributes Characteristics of entities or relationships. Denoted by ellipses in the model. Employee Department Name SSN Name Budget Chapter 1 Data Types in SQL Atomic types:  Characters:CHAR(20), VARCHAR(50)  Numbers: INT, BIGINT, SMALLINT, FLOAT  Others: MONEY, DATETIME, … Every attribute must have an atomic type Chapter 1 Relationships Represent associations between entities. Denoted by diamonds in the model. Employee works in Department Name SSN Start date Name Budget Chapter 1 Relationship Connectivity Constraints on the mapping of the associated entities in the relationship. Denoted by variables between the related entities. Generally, values for connectivity are expressed as “one” or “many” N 1 Employee work Department Name SSN Start date Name Budget Chapter 1 Connectivity one-to-one 1 1 Department has Manager one-to-many 1 N Department has Project many-to-many M N Employee works on Project Chapter 1 ER example Retailer wants to create an online webstore. The retailer requires information on: Customers Items Orders Chapter 1 Webstore Entities & Attributes Customers - name, credit card, address Items - name, price, inventory Orders - item, quantity, cost, date, status Name price date cost status credit card Customers Items Orders name address item quantity inventory Chapter 1 Webstore Relationships Identify the relationships. The orders are recorded each time a customer purchases items, so the customer and order entities are related. Each customer may make several purchases so the relationship is one-to-many 1 N Customer purchase Order Chapter 1 Webstore Relationships Identify the relationships. The order consists of the items a customer purchases but each item can be found in multiple orders. Since a customer can purchase multiple items and make multiple orders the relationship is many to many. M N Order consists Item Chapter 1  Webstore ER Diagram name credit card address Customers 1 purchase date status N Orders M consists Items N item quantity cost name price inventory Chapter 1 Logical Design to Physical Design Creating relational SQL schemas from entity- relationship models. Transform each entity into a table with the key and its attributes. Transform each relationship as either a relationship table (many-to-many) or a “foreign key” (one-to-many and many-to-many). Chapter 1 What is SQL ( ‘Structured Query Language’ )? SQL stands for ‘Structured Query Language’ SQL is the standard language for data definition and data manipulation for relational database systems. SQL is domain-specific language, NOT a general programming language  SQL is specialized to handle ‘structured data’ that follows relational model – data that incorporates relations among entities and variables.  Used to interact with relational databases to manage data: create, populate, modify, or destroy data. Also can manage data access Chapter 1 SQL Category 1. Data Query Language (DQL) - used to query data 2. Data Manipulation Language (DML) – used to create/modify/destroy data 3. Data Definition Language (DDL) – used to define database schema 4. Data Control Language (DCL) – used for security and access control Chapter 1 Most Important SQL Statements SELECT - extracts data from a database (DQL) UPDATE - updates data in a database (DML) DELETE - deletes data from a database (DML) INSERT - inserts new data into a database (DML) CREATE DATABASE - creates a new database (DDL) CREATE TABLE - creates a new table (DDL) DROP TABLE - deletes a table (DDL) Chapter 12/29/2024 1 SCC Research Data Metrics 51 Entity tables Transform each entity into a table with a key and its attributes. create table employee Employee (emp_no number, name varchar2(256), ssn number, primary key (emp_no)); Name SSN Chapter 1  Foreign Keys Transform each one-to-one or one-to-many relationship as a “foreign key”. Foreign key is a reference in the child (many) table to the primary key of the parent (one) table. create table department Department (dept_no number, name varchar2(50), 1 primary key (dept_no)); has create table employee (emp_no number, dept_no number, N name varchar2(256), ssn number, Employee primary key (emp_no), foreign key (dept_no) references department); Chapter 1 Foreign Key Department Accounting has 1 employee: dept_no Name Brian Burnett 1 Accounting 2 Human Resources Human Resources has 2 employees: 3 IT Nora Edwards Ben Smith IT has 3 employees: Employee Ajay Patel John O’Leary emp_no dept_no Name Julia Lenin 1 2 Nora Edwards 2 3 Ajay Patel 3 2 Ben Smith 4 1 Brian Burnett 5 3 John O'Leary 6 3 Julia Lenin Chapter 1  Many-to-Many tables Transform each many-to-many relationship as a table. The relationship table will contain the foreign keys to the related entities as well as any relationship attributes. create table project_employee_details Project (proj_no number, N emp_no number, start_date date, primary key (proj_no, emp_no), Start date has foreign key (proj_no) references project foreign key (emp_no) references employee); M Employee Chapter 1 Many-to-Many tables Project Project_employee_details proj_no Name proj_no emp_no start_date 1 Employee Audit 1 4 4/7/03 2 Budget 3 6 8/12/02 3 5 3/4/01 3 Intranet 2 6 11/11/02 3 2 12/2/03 2 1 7/21/04 Employee Employee Audit has 1 employee: emp_no dept_no Name Brian Burnett 1 2 Nora Edwards 2 3 Ajay Patel Budget has 2 employees: 3 2 Ben Smith Julia Lenin 4 1 Brian Burnett Nora Edwards 5 3 John O'Leary 6 3 Julia Lenin Intranet has 3 employees: Julia Lenin John O’Leary Ajay Patel Chapter 1