Database Systems Introduction PDF

# Database Systems ## Overview of Database Design Process The following are the main phases of database design: * **Requirements Collection and Analysis:** * Functional Requirements * Data Requirements * **Conceptual Design:** * Conceptual Schema (in a high-level data model) * **Logi...

# Database Systems ## Overview of Database Design Process The following are the main phases of database design: * **Requirements Collection and Analysis:** * Functional Requirements * Data Requirements * **Conceptual Design:** * Conceptual Schema (in a high-level data model) * **Logical Design (Data Model Mapping):** * Logical (Conceptual) Schema (in the data model of a specific DBMS) * **Physical Design:** * Internal Schema ## Example COMPANY Database * We need to create a database schema design based on the following simplified requirements of the COMPANY database: * **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. A department may have several locations. * **Each department controls a number of PROJECTS.** Each project has a unique name, unique number, and is located at a single location. ## Example COMPANY Database (Continued) * The database will store each **EMPLOYEE's** social security number, address, salary, sex, and birthdate. * Each employee works for one department but may work on several projects. * The DB will keep track of the number of hours per week that an employee currently works on each project. * It is required to keep track of the direct supervisor of each employee. * Each **employee may have a number of DEPENDENTS**. * For each dependent, the DB keeps a record of name, sex, birthdate, and relationship to the employee. ## Example COMPANY Database Schema The database schema is represented in the following table: | Table Name | Attributes | |---|---| | EMPLOYEE | Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno | | DEPARTMENT | Dname, Dnumber, Mgr\_ssn, Mgr\_start\_date | | DEPT\_LOCATIONS | Dnumber, Dlocation | | PROJECT | Pname, Pnumber, Plocation, Dnum | | WORKS\_ON | Essn, Pno, Hours | | DEPENDENT | Essn, Dependent\_name, Sex, Bdate, Relationship | ## Structured Query Language (SQL) Here is an example of how to use SQL to search for customers in a database: ```csharp using System.Linq; using System.Data.SqlClient; using Microsoft.EntityFrameworkCore; { namespace Query EF_MVC_Core public class DBCtx : DbContext { public DBCtx(DbContextOptions<DBCtx> options) : base(options) { } public DbSet<Customer> Customers { get; set; } public IQueryable<Customer> SearchCustomers(string contactName) { string sql = @"SELECT TOP 10 * FROM Customers WHERE ContactName LIKE '%' + @ContactName + '%'"; SqlParameter pContactName = new SqlParameter("@ContactName", contactName); return this.Customers.FromSql(sql, pContactName); } } } ``` ## Basic Definitions * **Database:** A collection of related data. * **Data:** Known facts that can be recorded and have an implicit meaning. * **Database Management System (DBMS):** A software package / system to facilitate the creation and maintenance of a computerized database. * **Database System:** The DBMS software together with the data itself. Sometimes, the applications are also included. ## Introduction * **Meta-data:** Describes the structure of the database (database definition or descriptive information). It is stored by the DBMS in the form of a database catalog or dictionary. * **Database catalog used by:** * DBMS software * Database users who need information about database structure ## Introduction (cont'd.) * **Query:** Causes some data to be retrieved. Some examples of queries include: * List the names of students who took the 'Database' course * List the prerequisites of the ‘Database' course * **Transaction:** May cause some data to be read, and some data to be written into the database. * An executing program or process that includes one or more database operations. * **Isolation property:** Each transaction appears to execute in isolation from other transactions. * **Atomicity property:** Either all the database operations in a transaction are executed or none are. ## Phases for Designing a Database * **Requirements specification and analysis:** This phase involves identifying the user requirements and defining the scope of the database. * **Conceptual design:** This phase involves creating a high-level model of the database, which is independent of any specific DBMS. * **Logical design:** This phase involves translating the conceptual schema into a specific data model, such as the relational model. * **Physical design:** This phase involves defining the physical storage structures for the database, such as the file organization and indexing. ## Data Abstraction * **Data abstraction:** Allows program-data independence and program-operation independence * **Conceptual representation of data:** Does not include details of how the data is stored or how operations are implemented. * **Data model:** Type of data abstraction used to provide conceptual representation. ## Insulation Between Programs and Data * **Program-data independence:** The structure of data files is stored in the DBMS catalog separately from access programs, making it possible to change the data structure without affecting the programs. * **Program-operation independence:** The operations are specified in two parts: * Interface: Includes the operation's name and data types of its arguments * Implementation: Can be changed without affecting the interface ## Support of Multiple Views of the Data * **View:** A subset of the database that contains virtual data derived from the database files but is not explicitly stored. This makes it possible for different users to see different versions of the data, depending on their needs. * **Multiuser DBMS:** Users have a variety of distinct applications, and it is necessary to provide facilities for defining multiple views so that each user can access only the data they need. ## Actors on the Scene * **Database administrators (DBAs):** Responsible for: * Authorizing access to the database * Coordinating and monitoring its use * Acquiring software and hardware resources * **Database designers:** Responsible for: * Identifying the data to be stored * Choosing appropriate structures to represent and store this data ## Actors on the Scene (cont'd.) * **End users:** People whose jobs require access to the database. Some examples include: * Casual end users * Naive or parametric end users * Sophisticated end users * Standalone users * **System analysts:** Determine the requirements of the end users. * **Application programmers:** Implement the specifications as programs. ## Workers behind the Scene * **DBMS system designers and implementers:** Design and implement the DBMS modules and interfaces as a software package. * **Tool developers:** Design and implement tools. * **Operators and maintenance personnel:** Responsible for running and maintaining the hardware and software environment for the database system. ## Main Topics in This Course * Introduction to Databases Chapter 1 and Chapter 2 * Chapter 3 Data Modeling Using Entity Relationship (ER) Model * Chapter 4 The Enhanced Entity-Relationship (EER) Model * Chapter 5 The Relational Data Model and Relational Database Constraints * Chapter 9 Relational Database Design by ER- and EER-to-Relational Mapping * Chapter 6 Basic Structured Query Language (SQL) * Chapter 7 More SQL: Complex Queries, Triggers, Views, and Schema Modification ## Fundamentals of Database Systems The course uses the textbook "Fundamentals of Database Systems", 7th edition by Elmasri and Navathe. The document covers key concepts related to database systems, including their structure, design principles, data models, query languages, and the different roles involved in database development and maintenance.