Chapter+1+-+The+Database+Basics.pdf

Chapter 1 The Database Basics Learning Outcomes Describe Relational Database and RDBMS Describe the use of SQL and MySQL with Relational databases Describe Data Definition Language and Data Manipulation Language  What is Database? Image result...

Chapter 1 The Database Basics Learning Outcomes Describe Relational Database and RDBMS Describe the use of SQL and MySQL with Relational databases Describe Data Definition Language and Data Manipulation Language  What is Database? Image result for database A database is a collection of related data for various uses. Used to maintain information about various types of objects (inventory), events (sales), people(customer) and places (retail store). Database Management System DBMS contains information about a particular enterprise Collection of interrelated data Set of programs to access the data An environment that is both convenient and efficient to use Database Applications: Banking: transactions Airlines: reservations, schedules Universities: registration, grades Sales: customers, products, purchases Online retailers: order tracking, customized recommendations Manufacturing: production, inventory, orders, supply chain Human resources: employee records, salaries, tax deductions Databases can be very large. Databases touch all aspects of our lives  Database Management System Is defined as a computer program that creates, modifies and queries the database. Specifically, it is designed to manage a database’s storage and retrieval of information. Image result for database management system Database Engine Storage manager: the component of a database system that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system. The storage manager is responsible for the interaction with the file manager. The raw data are stored on the disk using the file system provided by the operating system. The storage manager translates the various DML statements into low-level file-system commands It is, the storage manager is responsible for storing, retrieving, and updating data in the database. The storage manager components include: Authorization and integrity manager, Transaction manager, File manager and Buffer manager The storage manager implements several data structures as part of the physical system implementation: Data files, Data dictionaries and Indices Database Engine Query Processing 1. Parsing and translation 2. Optimization 3. Evaluation Database Engine Query Processing Alternative ways of evaluating a given query Equivalent expressions Different algorithms for each operation Cost difference between a good and a bad way of evaluating a query can be enormous Need to estimate the cost of operations Depends critically on statistical information about relations which the database must maintain Need to estimate statistics for intermediate results to compute cost of complex expressions Database Engine The transaction manager (TM) of a DBMS controls the execution of transactions. The DBMS supports concurrency and crash recovery by carefully scheduling user requests and maintaining a log of all changes to the database using TM. TM ensures that transactions request and release locks are according to a suitable locking protocol and schedules the execution transactions; the lock manager, keeps track of requests for locks and grants locks on database objects when they become available the recovery manager, is responsible for maintaining logs and restoring the system to a consistent state after crash. The disk space manager, buffer manager, and file and access method layers must interact with these components. Database Engine Transaction Management What if the system fails? What if more than one user is concurrently updating the same data? A transaction is a collection of operations that performs a single logical function in a database application Transaction-management component ensures that the database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and transaction failures. Concurrency-control manager controls the interaction among the concurrent transactions, to ensure the consistency of the database. Data Models Data Model is a collection of high-level data description constructs that hide many low-level storage details i.e. collection of tools for describing Data Data relationships Data semantics Data constraints ❖ Relational model ❖ Entity-Relationship data model (mainly for database design) ❖ Object-based data models (Object-oriented and Object-relational) ❖ Semi-structured data model (XML) ❖ Other older models: ❖ Network model ❖ Hierarchical model Relational Model Is a data model that stores information in the form of related two- dimensional tables. It allows designers and users to identify relationships at the time the database is created. Relational Model Advantages: Flexibility and scalability- able to handle changes in business and informational requirement quickly and easily. SAP, Oracle, Microsoft use this model as their foundation. Simplicity- easy to communicate to both database users and database developers. Reduced Information redundancy- requires each data to be recorded in one place which helps keep the information updated because it only has to be updated once in a database, which can help avoid data inconsistency. Relational Model Attribute – Each column in a table / properties which define a relation.(StudentNumber, Name, Address) Tables – In the Relational model, the relations are saved in the table format. It is stored along with its entities. A table has two properties rows and columns. Rows represent records and columns represent attributes. Tuple – It is nothing but a single row of a table, which contains a single record. Relation Schema: A relation schema represents the name of the relation with its attributes. Degree: The total number of attributes which in the relation is called the degree of the relation. Relational Model Concepts Cardinality – Total number of rows present in the Table. Column – The column represents the set of values for a specific attribute. Relation instance – Relation instance is a finite set of tuples in the RDBMS system. Relation instances never have duplicate tuples. Relation key – Every row has one, two or multiple attributes, which is called relation key. Attribute domain – Every attribute has some pre-defined value and scope which is known as attribute domain Relational Data Model Example Data Dictionary Describes the data fields in each database record such as field description, field length, field type (e.g., alphanumeric, numeric), etc. Best Practices for creating a Relational Model Data need to be represented as a collection of relations Each relation should be depicted clearly in the table Rows should contain data about instances of an entity Columns must contain data about attributes of the entity Cells of the table should hold a single value Each column should be given a unique name No two rows can be identical The values of an attribute should be from the same domain Fundamentals of Relational Databases Three Main Constructs: Table/Relation The primary construct, used for data storage with rows and columns much like a spreadsheet. Each table in a database represents either a class or a relationship between classes. Tables need to be properly linked to make a relational database. Column The columns in a table are called fields that represent the attributes or characteristics of the class relationship. Rows The rows in a table are called records or tuples. The records represent all the specific data values that are associated with one instance. Three Main Constructs: Image result for relational database  Operations in Relational Model Insert – is used to insert data into the relation Delete – is used to delete tuples from the table. Modify – allows you to change the values of some attributes in existing tuples. Select – allows you to choose a specific range of data. What is RDBMS? Relational Management System Manages data according to a relational model. Organizes and stores data in the form of tables. RDBMS Database Structure Data Storage Two-dimensional columns and rows Database Users and Administrators Database Database System Internals This Fig. shows various components of a database system and the connections among them  Database Architecture The architecture of a database systems is greatly influenced by the underlying computer system on which the database is running: Database systems can be centralized, or client-server, where one server machine executes work on behalf of multiple client machines. Database systems can also be designed to exploit parallel computer architectures. Distributed databases span multiple geographically separated machines.  Database Architecture ❑ Database applications are usually partitioned into two or three parts. In a two-tier architecture, the application resides at the client machine, where it invokes database system functionality at the server machine through query language statements Application program interface standards like ODBC and JDBC are used for interaction between the client and the server. ❑ In contrast, in a three-tier architecture, the client machine acts as merely a front end and does not contain any direct database calls. Instead, the client end communicates with an application server, usually through a forms interface. The application server in turn communicates with a database system to access data. The business logic of the application, which says what actions to carry out under what conditions, is embedded in the application server, instead of being distributed across multiple clients. Three-tier applications are more appropriate for large applications, and for applications that run on the WorldWideWeb. Database Architecture  Data Administrator The person responsible for the design, implementation, repair and security of a firm’s database. Manages the complete database of an organization.  Database Design The process of designing the general structure of the database: ❑ Logical Design – Deciding on the database schema. Database design requires that we find a “good” collection of relation schemas. Business decision – What attributes should we record in the database? Computer Science decision – What relation schemas should we have and how should the attributes be distributed among the various relation schemas? ❑ Physical Design – Deciding on the physical layout of the database  CUSTOMER TABLE Customer ID Customer Last Customer First Customer Name Name Address 602-966-1238 Dela Cruz Juan Tondo, Manila 602-252-7513 Silangan Lualhati Sampaloc, Manila SALES TABLE Invoice No. Customer ID Date Product No. 13131 602-966-1238 10/17/13 1233 13945 602-252-7513 12/28/13 1334 11995 602-966-1238 2/21/14 1233 Fundamentals of Relational Databases Keys and Relationships: Primary key is an attribute or combination of attributes that uniquely identify a specific row in a table. Foreign key in the relational database model serves as an attribute in one table that is a primary key in another table. A foreign key provides a logical relationship, or a link between two tables. Image result for relational database  CUSTOMER TABLE Customer ID Customer Last Customer First Customer Name Name Address 602-966-1238 Dela Cruz Juan Tondo, Manila 602-252-7513 Silangan Lualhati Sampaloc, Manila Primary Key Foreign Key SALES TABLE Invoice No. Customer ID Date Product No. 13131 602-966-1238 10/17/13 1233 13945 602-252-7513 12/28/13 1334 11995 602-966-1238 2/21/14 1233  SALES TABLE Invoice No. Customer ID Date Product No. 13131 602-966-1238 10/17/13 1233 13945 602-252-7513 12/28/13 1334 11995 602-251-7513 2/21/14 1233 Primary Key Foreign Key Primary Key PRODUCT TABLE Product No. Product Name Product Price 1233 Product A 399 1334 Product B 299 1233 Product A 399 ABC Store June 17, 2020 Sales Invoice Inv No. 13131 To: Juan dela Cruz Tondo, Manila 602-966-1238 Product No. Description Price Amount 1233 Product A 399.00 399.00 Total 399.00 Basic Requirements of Tables The Entity Integrity Rule: the primary key of a table must have data values (cannot be null). The Referential Integrity Rule: the data value for a foreign key must either be null or match one of the data values that already exist in the corresponding table. Each attribute in a table must have a unique name. Values of a specific attribute must be of the same type. Each attribute (column) of a record (row) must be single- valued. This requirement forces us to create a relationship table for each many-to-many relationship. All other non-key attributes in a table must describe a characteristic of the class (table) identified by the primary key. The Use of SQL with Databases Structured Query Language Standardized Language Based on English Language (Phrases) Categories of phrase types 1. DCL (Data Control Language) 2. DDL (Data Definition Language) 3. DML (Data Manipulation Language) 4. DQL (Data Query Language) Types of Structured Query Language(SQL) Commands MySQL MySQL, pronounced either "My S-Q-L" or "My Sequel," is an open source relational database management system. It is based on the Structure Query Language, which is used for adding, removing, and modifying information in the database. Standard SQL commands, such as ADD, DROP, INSERT, and UPDATE can be used with MySQL. MySQL Added-Value Powerful Extension of SQL Language Runs on many different Operating Systems Flexible and secure authorization system Support very large databases Very customizable Built for speed Free or Inexpensive Open source and Commercial Exceptional Support Available Summary Definition of Relational Databases and RDBMS RDBMS database structure The use of SQL with Relational Database DCL, DDL, DML, DQL