Lecture Notes: Fundamentals of Database Systems PDF

COMP1115 – Fundamentals of Database Systems Data Models, Schemas, and Instances  Database Management System (DBMS) A DBMS is a software application that enables users to efficiently store, manage, and retrieve data. Examples are: MS Access, MySQL, S...

COMP1115 – Fundamentals of Database Systems Data Models, Schemas, and Instances  Database Management System (DBMS) A DBMS is a software application that enables users to efficiently store, manage, and retrieve data. Examples are: MS Access, MySQL, SQL Server, etc. Adapted: Elmasri. Ramez and Navathe. Shamkant. B. (2010), fundamental of Database Systems, 6nd Edition, , Prentice Hall, ISBN 13: 978-0-136-08620-8  Key Components of DBMS Data: This is the raw information that needs to be stored and managed. Data can be of various types, such as text, numbers, dates, or multimedia. Database: A database is a structured collection of data organized to be easily accessed, managed, and updated. It's like a digital filing cabinet. DBMS Engine: The DBMS engine is the core of the system. It processes user requests, manages data storage, and ensures data integrity. SQL (Structured Query Language): SQL is the language used to communicate with a DBMS. It allows users to create, retrieve, update, and delete data in a database. Adapted: Elmasri. Ramez and Navathe. Shamkant. B. (2010), fundamental of Database Systems, 6nd Edition, , Prentice Hall, ISBN 13: 978-0-136-08620-8  DBMS Functionality Define the database (Structure, data types, constraints, etc.) Load the initial database contents on a secondary storage medium (Hard Disk) Manipulating the database Retrieval: Querying, generating reports Modification: Insertions, deletions and updates to its content Accessing the database through Web applications Adapted: Elmasri. Ramez and Navathe. Shamkant. B. (2010), fundamental of Database Systems, 6nd Edition, , Prentice Hall, ISBN 13: 978-0-136-08620-8  DBMS Functionality Processing and Sharing by a set of users and application programs Security measures to prevent unauthorized access Presentation and Visualization of data Maintaining the database and associated programs Adapted: Elmasri. Ramez and Navathe. Shamkant. B. (2010), fundamental of Database Systems, 6nd Edition, , Prentice Hall, ISBN 13: 978-0-136-08620-8  Data Models Data Model: A set of concepts to describe the structure of a database, the operations for manipulating these structures, and certain constraints(rules/conditions) that the database should Student ID Student Name GPA Mobile Number obey. 2304 Fatema 2.3 99885423 Data Model Structure and Constraints: 1212 Ahmad 2.9 77873212 0=4 Constructs are used to define the database 8 digits in Mobile structure (Elements and their relationship) Number Constraints specify some restrictions on valid data Data Models Data Model Operations: Operations are used to get/retrieve or update the data in the database Example show all the students with GPA more than 2 Student ID Student Name GPA Mobile Number 2304 Fatema 2.3 99885423 1212 Ahmad 2.9 77873212 Database Schema Database schema for a database is somehow similar to map/design for a house Database Database Schema  Database Schema Database Schema: The description of a database. Includes descriptions of the database structure, data types, and the constraints on the database. Schema Diagram: An illustrative display of (most aspects of) a database schema. Schema Construct: A component of the schema or an object within the schema, e.g., STUDENT, COURSE. Adapted: Elmasri. Ramez and Navathe. Shamkant. B. (2010), fundamental of Database Systems, 6nd Edition, , Prentice Hall, ISBN 13: 978-0-136-08620-8 Schema Diagram Example  Database State The actual data stored in a database at a particular moment in time. This includes the collection of all the data in the database. Also called database instance Adopted: Elmasri. Ramez and Navathe. Shamkant. B. (2010), fundamental of Database Systems, 6nd Edition, , Prentice Hall, ISBN 13: 978-0-136-08620-8  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. Distinction: The database schema does not change frequently while the database state changes every time the database is updated. Adapted: Elmasri. Ramez and Navathe. Shamkant. B. (2010), fundamental of Database Systems, 6nd Edition, , Prentice Hall, ISBN 13: 978-0-136-08620-8 Database Schema vs. Database State Database Schema Database State  DBMS Languages Data Definition Language (DDL): Used to define the structure (Conceptual Schema) of a database. For example Creating a new table Adding a column to a table Changing data type of a column in a table Data Manipulation Language (DML): Used to insert, retrieve, update or delete the data in a database. For Example, Insert data into a table Update data in a table  Database System Utilities Loading data stored in files into a database. Includes data conversion tools. Backing up the database periodically. Reorganizing database file structures. Report generation utilities. Performance monitoring utilities. Other functions, such as sorting, user monitoring, data compression, etc. Adapted: Elmasri. Ramez and Navathe. Shamkant. B. (2010), fundamental of Database Systems, 6nd Edition, , Prentice Hall, ISBN 13: 978-0-136-08620-8  Three-tier client-server architecture Adopted: Elmasri. Ramez and Navathe. Shamkant. B. (2010), fundamental of Database Systems, 6nd Edition, , Prentice Hall, ISBN 13: 978-0-136-08620-8  Practice Q1. Define the following – Data Model – Data Model Constructs – Database Schema – Valid State of a Database – Data Manipulation Language (DML) Q2. Describe the functionality of DBMS. Q3. Identify Construct, Constraints and Operations in the following – A database contains a table with the name STUDENT that has three columns (Student_ID, Student_Name, Student_Address) – Show the list of students who belong to Sohar – The student’s name must not contain any digit Q4. Classify each of the following into DML or DDL commands – Create a table with the name COURSES having three columns: Course_Code, Course_Name, and Units – Insert “COMP1115, Databases, 2” into the table created in part a. – Update the Course_Name for the data inserted in part b to “Fundamentals of Databases”  Practice Q1. Define the following – Data Model Constraints – Data Model Operations – Database State – Data Definition Language (DDL) Q2. Differentiate between Database Schema and Database State with examples. Q3. Identify Construct, Constraints and Operations in the following – Delete the record of a student with the name FATEMA. – The student’s mobile number must contain at least 8 digits – A database contains two tables STUDENTS and COURSES. Q4. Classify each of the following into DML or DDL commands – Create a table with the name STUDENTS having three columns: Student_ID, Student_Name, and Student_Address – Insert “201203, Sheikha, Databases, Saham” into the table created in part a. – Delete the data for student “Sheikha” inserted in part b.