Database Management - Introduction to Databases PDF

DATABASE MANAGEMENT INTRODUCTION TO DATABASES Lecture 1  Table Of Content Database Database Query basics systems languages Database MySQL MySQL design Workbench D...

DATABASE MANAGEMENT INTRODUCTION TO DATABASES Lecture 1  Table Of Content Database Database Query basics systems languages Database MySQL MySQL design Workbench Database basics Data Data is numeric, textual, visual, or audio information that describes real-world systems. Data is collected and processed to aid in a variety of tasks, such as forecasting weather, analyzing financial investments, and tracking the global spread of pandemics. Concepts Data Data can vary in several important ways: Scope. The amount of data produced and collected can vary. Example: A small business might track an inventory of a few thousand items, but a large commerce website might track billions of items. Format. Data may be produced as numbers, text, image, audio, or video. Example: A phone's proximity sensor generates raw numbers, and a satellite captures images. Access. Some data sources are private while others are made publicly available. Example: A retail company may use private customer data to discover purchasing behavior patterns, but a government may be required by law to share certain data sets.  Concepts Databases A database is a collection of data in a structured format. Modern databases are invariably stored on computers. The database structure ensures that similar data is stored in a standardized manner. Many modern databases contain trillions of bytes of data and support thousands of simultaneous users. A database management system or DBMS, is software that reads and writes data in a database. Database systems ensure data is secure, consistent, and available at all times. A query is a request to retrieve or change data in a database. A query language is a specialized programming language, designed specifically for database systems. Query languages read and write data efficiently, and differ significantly from general-purpose languages such as Python, Java, and C++. Database software is organized in layers. Applications interact with a query language on one layer, and a query language interacts with a database system on another layer. Other software layers, such as the operating system, are beyond the scope of this material.  Concepts Database roles We can interact with databases in a diverse of roles: A database administrator is responsible for securing the database system against unauthorized users. A database administrator enforces procedures for user access and database system availability. A database designer determines the format of each data element and the overall database structure. Database designers must balance several priorities, including storage, response time, and support for rules that govern the data. A database programmer develops computer programs that utilize a database. Database programmers write applications that combine database query languages and general-purpose programming languages. A database user is a consumer of data in a database. Database users request, update, or use stored data to generate reports or information. Database users usually access the database via applications but can also submit queries directly to the database system. The database designer establishes the structure of the database and determines the data to be collected and stored. The database administrator ensures the database is available and secure. A database programmer uses query languages and programming languages to develop applications for database users. Database users are the primary consumers of database data through applications or with query languages.  Questions 1) Which role is responsible for Database administrator Database designer providing access to the Database programmer database? Database users Database administrator 2) Which role is responsible for Database designer defining the detailed database Database programmer design? Database users 3) Which role uses an application Database administrator to query a database and generate Database designer Database programmer a report? Database users  Answers 1) Which role is responsible for providing access to the Database administrator database? 2) Which role is responsible for defining the detailed database Database designer design? 3) Which role uses an application to query a database and generate Database users a report? Database systems File systems and database systems Small databases that are shared by one or two users can be operated in a text file or spreadsheet. Text files and spreadsheets are not sufficient and efficient, however, as databases increase in size, complexity, and use.  Concepts File systems and database systems Large, complex databases that are shared by many users have special requirements: Performance. When many users and applications simultaneously access large databases, query response time decreases rapidly. Database systems must maintain fast response times by structuring data properly on storage media and processing queries efficiently. Authorization. Many database users should have limited access to specific tables, columns, or rows of a database. Database systems must authorize individual users to access specific data. Security. Database systems must ensure authorized users only access approved and verified data. Database systems must also cipher data and constrain access. Rules. Database systems must ensure data is consistent with structural rules. Example: When multiple copies of data are stored in different locations, copies must be synchronized as data is updated. Recovery. Computers, database systems, and individual transactions occasionally fail. Database systems must recover from failures and restore the database to a consistent state without deprivation of data.  Concepts Architecture The architecture of a database system describes the internal components and the relationships between components. The query processor interprets queries, creates a plan to alternate the database or retrieve data, and returns query results to the application. The query processor performs query optimization to ensure the most efficient instructions are executed on the data. The storage manager transforms the query processor instructions into low-level file-system commands that alternate or retrieve data. Database sizes range from megabytes to many terabytes, it is allowed to use indexes to quickly locate data. The transaction manager ensures transactions are properly executed. The transaction manager prevents conflicts between concurrent transactions. The transaction manager also restores the database to a consistent state in the event of a transaction or system failure. The log is a file containing a complete record of all inserts, updates, and deletes processed by the database. The transaction manager writes log records before applying changes to the database. In the event of a failure, the transaction manager uses log records to restore the database. The catalog (data dictionary), is a directory of tables, columns, indexes, and other database objects. Other components use catalog information to process and execute queries. Note: Some database systems do not support transactions and therefore have no transaction manager. Note: The storage manager implementation depends on the physical structure of data on storage media.  Concepts Architecture 1) A database system consists of a query processor, storage manager, transaction manager, log, and catalog. 2) An application sends queries to the query processor. The query processor uses information from the catalog to perform query optimization. 3) The storage manager translates the query processor instructions into file-system commands and uses an index to quickly locate the requested data. 4) The transaction manager logs insert, update, and delete queries, and the result is sent back to the application.  Questions 1) The query processor has True direct access to the database False data on storage media. 2) Without query optimization, the storage manager cannot True retrieve the database data. False 3) The catalog allows the storage True manager to quickly locate the False requested data. 4) Every database query must be logged by the transaction manager True to recover the database in the event False of a system failure.  Answers 1) The query processor has direct access to the database False data on storage media. All access to the database data must go through the storage manager. 2) Without query optimization, False the storage manager cannot Query optimization ensures the most efficient retrieve the database data. instructions are sent to the storage manager, but the storage manager can retrieve the data with efficient or inefficient instructions. 3) The catalog allows the storage False manager to quickly locate the The catalog just describes the database. The requested data. index helps locate data quickly by providing information about where on the storage media certain pieces of data can be found. 4) Every database query must be False logged by the transaction manager All inserts, updates, and deletes must be logged to recover the database in the event in order to correctly restore database changes. of a system failure. Reads are not needed to restore database  Concepts Relational Database Most leading database systems are relational. A relational database stores data in tables, columns, and rows, similar to a spreadsheet. All data in a column has the same format. All data in a row represents a single object, such as a person, place, product, or activity. All relational database systems support the SQL query language. SQL stands for Structured Query Language and includes statements that read and write data, create and delete tables, and administer the database system. Relational systems are ideal for databases that require an accurate record of every transaction, such as banking, airline reservation systems, and student records. Query Languages A database system responds to queries written in a query language. A query is a command for a database that typically inserts new data, retrieves data, updates data, or deletes data from a database. A query language is a computer programming language for writing database queries.  Concepts Writing queries with SQL Structured Query Language, or SQL, is the standard query language of relational database systems. The SQL standard is sponsored by the American National Standards Institute (ANSI) and the International Standards Organization (ISO). An SQL statement is a database command, such as a query that inserts, retrieves, updates, or deletes data: INSERT inserts rows into a table. SELECT retrieves data from a table. UPDATE modifies data in a table. DELETE deletes rows from a table. Terminology The four common queries are sometimes referred to as CRUD operations, an acronym for Create, Read, Update, and Delete data. Concepts A bank database possesses an Account table with three columns: ID, Name, and Balance. The Account table has two rows that store Raul and Mai's account data. The INSERT statement adds a new row with Ethan's account data. The SELECT statement retrieves the names of accounts with a balance larger than $3000. The UPDATE statement change's Raul's balance from 3300 to 4500. The DELETE statement deletes Mai from the database.  Questions Refer to the Account table below. ID Name Balance 2) Which name is retrieved by the following Tom Cruise SELECT statement? Tom Hanks 100 Tom Cruise 5500 SELECT Name FROM Account WHERE Tom Hardy Balance < 6000; Tom Holland 105 Tom Hanks 15000 3) Whose balance does the following UPDATE Tom Cruise 120 Tom Hardy 12510 statement change? Tom Hanks UPDATE Account SET Balance = 9550 Tom Hardy WHERE ID = 100; Tom Holland 1) What is Tom Holland balance in the following INSERT statement? INSERT INTO Account VALUES (200, Tom Cruise 'Tom Holland', 4300); 4) Who is deleted by the following DELETE Tom Hanks statement? Tom Hardy DELETE Account WHERE ID = 125; Tom Holland No one 4300 4100 Unknown Everyone  Answers Refer to the Account table below. ID Name Balance 2) Which name is retrieved by the following SELECT statement? SELECT Name FROM Account WHERE Tom Cruise 100 Tom Cruise 5500 Balance < 6000; 105 Tom Hanks 15000 3) Whose balance does the following UPDATE 120 Tom Hardy 12510 statement change? UPDATE Account SET Balance = 9550 Tom Cruise WHERE ID = 100; 1) What is Tom Holland balance in the following INSERT statement? INSERT INTO Account VALUES (200, 'Tom Holland', 4300); 4) Who is deleted by the following DELETE statement? DELETE Account WHERE ID = 125; No one 4300  Concepts Creating tables with SQL The SQL CREATE TABLE statement creates a new table by specifying the table and column names. Each column is assigned a data type that indicates the format of column values. Data types can be numeric, textual, or complex. Example: INT stores integer values. DECIMAL stores fractional numeric values. VARCHAR stores textual values. DATE stores year, month, and day. Note: Some data types are followed by one or two numbers in parentheses, indicating the size of the data type. Ex: VARCHAR(10) indicates ten characters. DECIMAL(10, 3) indicates ten significant digits, including three after the decimal point.  Concepts The CREATE TABLE statement names the new table "Employee". The column names and data types are separated by commas. Database design Analysis A database design is a specification of database objects such as tables, columns, data types, and indexes. Database design also refers to the process used to develop the specification. For small, simple databases, the database design process can be informal. For large, complex databases, the process has three phases: 1. Analysis 2. Logical design 3. Physical design  Concepts Analysis The analysis phase specifies database requirements regardless to a specific database system. Requirements are represented as entities, relationships, and attributes. An entity is a person, place, activity, or thing. A relationship is a link between entities, and an attribute is a descriptive property of an entity. Entities, relationships, and attributes are depicted in ER diagrams: Rectangles represent entities. Entity names appear at the top of rectangles. Lines between rectangles represent relationships. Text inside rectangles and below entity names represent attributes. ER diagrams are usually supplemented by textual descriptions of entities, relationships, and attributes.  Concepts Logical design The logical design phase implements database requirements in a specific database system. For relational database systems, logical design converts entities, relationships, and attributes into tables, keys, and columns. A key is a column used to identify individual rows of a table. Tables, keys, and columns are specified in SQL with CREATE TABLE statements. The logical design is depicted in a table diagram. Table diagrams are similar to ER diagrams but more detailed: Rectangles represent tables. Table names appear at the top of rectangles. Text within rectangles and below table names represents columns. Solid bullets ( ) indicate key columns. ○ Empty bullets ( ) and arrows indicate columns that refer to keys.  Concepts Physical design The physical design phase adds indexes and specifies how tables are organized on storage media. Example: Rows of a table may be sorted on the values of a column and stored in sort order. Physical design is specified with SQL statements such as CREATE INDEX and, like logical design, is specific to a database system. In relational databases, logical and physical design affect queries differently. Logical design impacts the query result. Physical design impact query processing efficiency but never impacts the query result. The principle that physical design never affects query results is called data independence. Data independence allows database designers to tune query performance without alternations to application programs. When database designers modify indexes or row order, applications run faster or slower but always generate the same results.  Questions 1) Implementation of database Logical Design requirements as tables, keys, and Database Design columns in a specific database Analysis system Physical Design Logical Design 2) The overall process of Database Design determining and implementing Analysis database requirements Physical Design Logical Design 3) Specification of database Database Design requirements without regard to Analysis implementation Physical Design Logical Design Database Design 4) Affects query performance but not Analysis query results Physical Design  Answers 1) Implementation of database requirements as tables, keys, and Logical Design columns in a specific database system 2) The overall process of Database Design determining and implementing database requirements 3) Specification of database Analysis requirements without regard to implementation 4) Affects query performance but not Physical Design query results  MySQL MySQL is a leading relational database system sponsored by Oracle. MySQL is relatively easy to install and use, yet has many advanced capabilities. MySQL runs on all major operating systems, including Linux, Unix, Mac OS, and Windows. For these reasons, MySQL is one of the most popular database systems. We will only be using MySQL community server 8.0.X for our class, ensure that you install that version not any other. You can find the installer for Windows here https://dev.mysql.com/downloads/mysql/ Choose Microsoft Windows You can find the files for MAC here https://dev.mysql.com/downloads/mysql/ Choose macOS MySQL is available in two editions: MySQL Community, commonly called MySQL Server, is a free edition. MySQL Server includes a complete set of database services and tools, and is suitable for non-commercial applications such as education. MySQL Enterprise is a paid edition for managing commercial databases. MySQL Enterprise includes MySQL Server and additional administrative applications.  Concepts MySQL When installing MySQL Server, the user must enter a password for the root account, the administrative account that has full control of MySQL. Other database user accounts may optionally be created. After installation, MySQL Server executes as a service in the background. MySQL Server automatically initiates and terminates when the operating system starts and stops. You can use this link to look over for your reference: https://dev.mysql.com/doc/  Concepts MySQL MySQL Command-Line Client The MySQL Command-Line Client is a text interface included in the MySQL Server download. The Command-Line Client allows developers to connect to the database server, perform administrative functions, and execute SQL statements. To run the Command-Line Client, a user must first open a Command Prompt on Windows or a Terminal on a Mac: Windows: Click the Start button in the Taskbar, type "cmd", then click Command Prompt. Mac: Click on the Terminal application, usually found in the Applications > Utilities folder. When MySQL Command-Line Client is started with the root account, the user is prompted to enter the root account password. Then Command-Line Client attempts to connect to the database server running on the local machine.  Concepts Picture 1 Picture 2 From a command-line prompt, the user starts the MySQL Command-Line Client. The -u option names the account, and -p indicates a password must be entered. The user enters the root password established during installation. After a successful login, the user is presented a mysql prompt. Commands are entered one by one on the command line. The pictures show the user typing SQL commands that use the 'asteroids' database, this database was installed on my machine. We will be useng the other database and i will provide it for you.  Concepts MySQL Workbench MySQL Workbench is installed with MySQL Server and allows developers to execute SQL commands using an editor. It possesses a graphical user interface. When MySQL Workbench is started, the user can connect to MySQL Server running on the local machine or on the network. The picture displays the MySQL Workbench home screen on Mac. The Windows version has some minor divergences. Clicking on the box labeled Local Instance MySQL80 connects to MySQL Server running on the same computer as MySQL Workbench. Concepts The pictures show the user typing SQL commands that use the 'asteroids' database, this database was installed on my machine. We will be using the other database and i will provide it for you.  Concepts Please feel free to use the following links for exploration and installation all essential software tools MySQL download MySQL installation video MySQL documentation - home page MySQL documentation - installation MySQL documentation - Workbench THANK YOU The end of the chapter 1 References: Effective SQL. 61 Specific Ways to Write Better SQL (John L. Viescas, Douglas J. Steele, Ben G. Clothier) Zybooks Fundamentals of Database Systems 7th Edition (Ramez Elmasri, Shamkant Navathe)