IMAN-MIDTERMS-REVIEWER PDF - Database Concepts and SQL

DDL/DML Structured Query Language (SQL) - Popular and widely used language for retrieving and manipulating database data - Developed in mid-1970s under the name SEQUEL - Renamed SQL in 1980 - Used by most DBMSs Data Definition Language (DDL) - To create and modify t...

DDL/DML Structured Query Language (SQL) - Popular and widely used language for retrieving and manipulating database data - Developed in mid-1970s under the name SEQUEL - Renamed SQL in 1980 - Used by most DBMSs Data Definition Language (DDL) - To create and modify the structure of objects in a database. - Create new database - Create new tables in database - Create stored procedures in a database - Retrieve data from a database - Insert records in a database - Update records in a database - Delete records from database - Create views in database - Set permission on tables, procedures (advance), and views - CREATE, ALTER, DROP, etc Data Manipulation Language (DML) - A set of computer programming language that add, delete, and modify data in a database. - SELECT, ORDER BY, WHERE, SELECT DISTINCT, AND, OR, IN - BETWEEN (view) o Joins o Inner joins o Left joins o Right join SQL Language 1. Data definition language 2. Data manipulation language 3. Data control language – manage user access and permissions to a database (GRANT, REVOKE) 4. Transaction control language – to manage transactions in the database (COMMIT, ROLLBACK, SAVEPOINT) Data types 1. Number(L, D), integer, smallint, decimal(L, D) 2. Char(L), varchar(L), varchar2(L) 3. Date, time, timestamp 4. Real, double, float 5. Interval day to hour  RELATIONAL SET OPERATORS - Defines theoretical way of manipulating table contents using relational operators - SELECT - PROJECT - JOIN - INTERSECT - UNION - DIFFERENCE - PRODUCT - DIVIDE Data dictionary - Provides detailed accounting of all tables found within the user/designer-created database - Contains (at least) all the attribute names and characteristics for each table in the system - Contains metadata: data about data System catalog - Contains metadata - Detailed system data dictionary that describes all objects within the database RELATIONAL DB Database concepts: - Entity - Attribute - Relationship - Functional dependence - Primary key A Logical View of Data Relational Model - View data logically rather than physically Table - Structural and data independence - Resembles a file conceptually Relational database model is easier to understand than hierarchical and network models Types of Keys - Composite key - Key attribute - Superkey  - Candidate key - Entity integrity - Nulls - Foreign key - Referential integrity - Secondary key Entity integrity - Requirement o All primary key entries are unique, and no part of a primary key may be null - Purpose o Each row will have a unique identity, and foreign key values can properly reference primary key values - Example o No invoice can have a duplicate number, nor can it be null. In short, all invoices are uniquely identified by their invoice number Referential integrity - Requirement o A foreign key may have either a null entry, as long as it is not a part of its table’s primary key, or an entry that matches the primary key value in a table to which it is related. (every non-null foreign key value must reference an existing primary key value.) - Purpose o It is possible for an attribute not to have a corresponding value, but it will be impossible to have an invalid entry. The enforcement of the referential integrity rule makes it impossible to delete a row in one table whose primary key has mandatory matching foreign key values in another table - Example o A customer might not yet have an assigned sales representative (number), but it will be impossible to have an invalid sales representative (number). RELATIONAL DB (continuation) / DB DESIGN Database design - Process of determining the particular tables and columns that will comprise a database. Relational database - Collection of tables Entity - Like a noun - Person, place, thing, or event Attribute  - Like an adjective or adverb - Property of an entity Relationship - Association between entities o 1:M ▪ Relational modeling ideal ▪ Should be the norm in any relational database design o 1:1 ▪ Should be rare in any relational database design o M:N ▪ Cannot be implemented as such in the relational model ▪ Can be changed into 1:M relationships CHAPTER 4 RELATIONAL MODEL CHARACTERISTICS Relational Model - View data logically rather than physically Table - Structural and data independence - Resembles a file conceptually Relational database model is easier to understand than hierarchical and network models. Tables and Their Characteristics - Logical view of relational database is based on relation o Relation thought of as a table - Table: 2d structure composed or rows and columns o Persistent representation of logical relation - Contains group of related entities (entity set) Tuple - A finite ordered list of elements. Attribute Domain - The set of values allowed. Keys - One or more attributes that determine other attributes - Ensure data integrity and allow for efficient searching and sorting of records in a DBMS. - Each row in a table must be uniquely identifiable. o Key’s role is based on determination o Functional dependence Primary key - A unique identifier for each record Foreign key - Links records between tables. Types of Keys Composite key - Combination of two or more fields in a database table that uniquely identifies each record. - Ensures that each record is unique and can be easily retrieved. Key attribute - Any attribute that is part of a key - Used as a key to access or search for records in the table. - Can be used to ensure data integrity by preventing duplicate or incorrect records from being entered into the table. Superkey - Any key that uniquely identifies each row - Can include more fields than the minimum required to uniquely identify a record. - Used to help identify unique records in a table and can be used to create primary keys, which are the minimum set of fields required to uniquely identify each record in a table. - Can also be used as a foreign key to link records between tables. Candidate key - A superkey without unnecessary attributes - It is a potential candidate for use as a primary key, which is the minimum set of fields required to uniquely identify each record in a table. - A table can have multiple candidate key but only one can be chosen as the primary key. - Can represent: o An unknown attribute value o A known but missing attribute value o A “not applicable” condition - Can create problems when functions such as COUNT, AVERAGE, and SUM are used. - Can create logical problems when relational tables are linked. Entity integrity - Each row (entity instance) in the table has its own unique identity. Nulls  - No data entry - Not permitted in primary key - Should be avoided in other attributes Controlled redundancy - Makes the relational database work - Tables within the database share common attributes o Enables tables to be linked together - Multiple occurrences of values not redundant when required to make the relationship work - Redundancy exists only when there is unnecessary duplication of attribute values. - It is a technique used in DBMS to improve data efficiency and performance. Foreign key - An attribute whose values match primary key values in the related table. Referential integrity - FK contains a value that refers to an existing valid tuple (row) in another relation Secondary key - Key used strictly for data retrieval purposes Integrity Rules - Enforce the accuracy and consistency of data in a database. - Can be defined by the database admin and can include rules such as requiring a unique value for a primary key, limiting the type of data that can be entered into a field, and establishing relationships between tables using foreign keys. - Ensure that data is entered correctly and that the data remains accurate over time which is important for the overall efficiency and reliability of the database. - Designers use flags to avoid nulls o Flags: indicate absence of some value Relational Set Operators Relational algebra - A mathematical language used to manipulate data in a DBMS. - Uses symbols and operators to perform operation on tables. - Defines theoretical way of manipulating table contents using relational operators - Use of relational algebra operators on existing relations produces new relations: o SELECT – used to choose certain rows from a table of specific criteria o PROJECT – used to choose certain columns o JOIN – used to combine two or more tables based on a common field. o INTERSECT o UNION o DIFFERENCE o PRODUCT o DIVIDE - An important tool for working with databases, and it helps ensure that data is organized and presented in a logical and efficient manner. Natural Join - Links tables by selecting rows with common values in common attributes (join columns) Equijoin - Links tables on the basis of an equality condition that compares specified columns Theta join - Any other comparison operator is used Inner join - Only returns matched records from the tables that are being joined Outer join - Matched pairs are retained, and any unmatched values in other table are left null  Left outer join - Yields all of the rows in the CUSTOMER table. - Including those that do not have a matching value in the AGENT table Right outer join - Yields all of the rows in the AGENT table - Including those that do not have matching values in the CUSTOMER Table Data Dictionary - Contains information about the database structure - Provides detailed accounting of all tables found within the user/designer-created database - Contains (at least) all the attribute names and characteristics for each table in the system - Contains metadata: data about data - Like a roadmap or a directory of the database System Catalog - Contains metadata - Detailed system data dictionary that describes all objects within the database - Contains information about the physical storage of the database, including how data is organized and how it can be accessed. - Like a blueprint of the database. Homonym - Indicates the use of the same name to label different attributes - Can create confusion and ambiguity Synonym - Opposite if a homonym - Indicates the use of different names to describe the same attribute - Can lead to redundancy and inefficiency Relationships within the Relational Database 1:M relationship - Relational modeling ideal - Should be the norm in any relational database design - Relational database norm - Found in any database environment  - 1:1 relationship - Should be rare in any relational database design - One entity related to only one other entity, and vice versa - Sometimes means that entity components were not defined properly - Could indicate that two entities actually belong in the same table - Certain conditions absolutely require their use - M:N relationships - Cannot be implemented as such in the relational model - Can be changed into 1:M - Implemented by breaking it up to produce a set of 1:M rs - Avoid problems inherent to M:N rs by creating a composite key o Includes as foreign keys the primary keys of tables to be linked -  - Data Redundancy Revisited - Data redundancy leads to data anomalies o Can destroy the effectiveness of the database - Foreign keys o Control data redundancies by using common attributes shared by tables o Crucial to exercising data redundancy control - Sometimes, data redundancy is necessary Indexes - Orderly arrangement to logically access rows in a table - Index key o Index’s reference point o Points to data location identified by the key - Unique index o Index in which the index key can have only one pointer value (row) associated with it - Each index is associated with only one table Codd’s Relational Database Rules - In 1985, Codd published a list of 12 rules to define a relational database system o Products marketed as “relational” that did not meet minimum relational standards - Even dominant database vendors do not fully support all 12 rules Codd’s Twelve Rules - Set of 13 rules (numbered 0 to 12) - Proposed by Edgar F. Codd o A pioneer of the relational model for databases, designed to define what is required from a DBMS in order for it to be considered relational - Set of guidelines that define what an RDBMS should look like. - State that RDBMS must have certain features such as tables with unique names, each containing data that relates to a specific subject or entity. Summary - Tables are basic building blocks of a relational database - Keys are central to the use of relational tables - Keys define functional dependencies o Superkey o Candidate key o Primary key o Secondary key o Foreign key - Each table row must have a primary key that uniquely identifies all attributes - Tables are linked by common attributes - The relational model supports relational algebra functions o SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT, DIVIDE - Good design begins by identifying entities, attributes, and relationships. - 1:1, 1:M, M:N