Lecture 5. Data base systems. Basics of database systems: concept and architecture. Data models. Basics of SQL. Lecture 5. Data base systems. Basics of database systems: concept and architecture. Data models. Basics of SQL. Information Communication technology

What is databases A set of information held in a computer Oxford English Dictionary One or more large structured sets of persistent data, usually associated with software to update and query the data Free On-Line Dictionary of Computing A collection of data arranged for ease and speed of search and retrieval Dictionary.com Nowadays we do not imagine the working of many financial, introduce, trade and other organizations. Databases allows structure, store information and retrieval its in optimize view.

Databases Web indexes Web indexes Library catalogues Library catalogues Medical records Medical records Bank accounts Bank accounts Stock control Stock control Personnel systems Personnel systems Product catalogues Product catalogues Telephone directories Telephone directories Train timetables Train timetables Airline bookings Airline bookings Credit card details Credit card details Student records Student records Customer histories Customer histories Stock market prices Stock market prices Discussion boards Discussion boards and so on… and so on…

Database Systems A database system consists of A database system consists of Data (the database) Data (the database) Software Software Hardware Hardware Users Users We focus mainly on the software We focus mainly on the software Database systems allow users to Database systems allow users to Store Update Retrieve Organize Protect their data.

Database Users End users End users Use the database system to achieve some goal Use the database system to achieve some goal Application developers Application developers Write software to allow end users to interface with the database system Write software to allow end users to interface with the database system Database Administrator (DBA) Database Administrator (DBA) Designs & manages the database system Database systems programmer Database systems programmer Writes the database software itself

Database Management Systems A database is a collection of information A database is a collection of information A database management system (DBMS) is the software than controls that information A database management system (DBMS) is the software than controls that information Examples: Examples: Oracle DB2 (IBM) MS SQL Server MS Access Ingres PostgreSQL MySQL

What the DBMS does Provides users with Provides users with Data definition language (DDL) Data definition language (DDL) Data manipulation language (DML) Data manipulation language (DML) Data control language (DCL) Data control language (DCL) Often these are all the same language Often these are all the same language DBMS provides DBMS provides Integrity Security Data independence Data Dictionary Data Dictionary Describes the database itself

Hierarchical Network Relational Essential Types of data models Data model

The Relational Model Each attribute has a unique name within an entity Each attribute has a unique name within an entity All entries in the column are examples of it All entries in the column are examples of it Each row is unique Each row is unique Ordering of rows and columns is unimportant Ordering of rows and columns is unimportant Each position is limited to a single entry. Each position is limited to a single entry.

Relational Systems Problems with early databases Problems with early databases Navigating the records requires complex programs Navigating the records requires complex programs There is minimal data independence There is minimal data independence No theoretical foundations No theoretical foundations Then, in 1970, E. Codd wrote A Relational Model of Data for Large Shared Databanks and introduced the relational model Then, in 1970, E. Codd wrote A Relational Model of Data for Large Shared Databanks and introduced the relational model

Relational Systems Information is stored as tuples or records in relations or tables Information is stored as tuples or records in relations or tables There is a mathematical theory of relations There is a mathematical theory of relations Most modern DBMS are based on the relational model Most modern DBMS are based on the relational model The relational model covers 3 areas: The relational model covers 3 areas: Data structure Data integrity Data manipulation

Database Operations Refers to the most common Database Operations: Refers to the most common Database Operations: Create Create Read Read Update Update Delete Delete Operations occur at all levels: Tables, Records, Columns Operations occur at all levels: Tables, Records, Columns

Database Tables Tables represent entities Tables represent entities Tables are always named in the singular, such as: Vehicle, Order, Grade, etc. Tables are always named in the singular, such as: Vehicle, Order, Grade, etc. It consists of rows and colomns It consists of rows and colomns

Attributes Characteristics of an entity Characteristics of an entity Examples: Examples: Vehicle (VIN, color, make, model, mileage) Vehicle (VIN, color, make, model, mileage) Student (SSN, Fname, Lname, Address) Student (SSN, Fname, Lname, Address) Fishing License (Type, Start_date, End_date) Fishing License (Type, Start_date, End_date)

Database Table Example Figure 2: An improved database table.. Figure 1: A simple – and flawed – table design.

Database Views A View is an individuals picture of a database. It can be composed of many tables, unbeknownst to the user. A View is an individuals picture of a database. It can be composed of many tables, unbeknownst to the user. Its a simplification of a complex data model Its a simplification of a complex data model It provides a measure of database security It provides a measure of database security Views are useful, primarily for READ-only users and are not always safe for CREATE, UPDATE, and DELETE. Views are useful, primarily for READ-only users and are not always safe for CREATE, UPDATE, and DELETE.

Database Keys Primary Key - Indicates uniqueness within records or rows in a table. Primary Key - Indicates uniqueness within records or rows in a table. Foreign Key - the primary key from another table, this is the only way join relationships can be established. Foreign Key - the primary key from another table, this is the only way join relationships can be established. There may also be alternate or secondary keys within a table. There may also be alternate or secondary keys within a table.

Relational Database Management System (RDBMS) NameAddressParcel # John Smith18 Lawyers Dr T. Brown14 Summers Tr Table A Table B Parcel #Assessed Value , ,000

Using SQL- Structured Query Language SQL is a standard database protocol, adopted by most relational databases SQL is a standard database protocol, adopted by most relational databases Provides syntax for data: Provides syntax for data: Definition Definition Retrieval Retrieval Functions (COUNT, SUM, MIN, MAX, etc) Functions (COUNT, SUM, MIN, MAX, etc) Updates and Deletes Updates and Deletes

SQL Examples CREATE TABLE SALESREP CREATE TABLE SALESREP Item definition expression(s) Item definition expression(s) {item, type, (width)} {item, type, (width)} DELETE table DELETE table WHERE expression WHERE expression

Data Retrieval SELECT list FROM table WHERE condition SELECT list FROM table WHERE condition list - a list of items or * for all items list - a list of items or * for all items WHERE - a logical expression limiting the number of records selected WHERE - a logical expression limiting the number of records selected can be combined with Boolean logic: AND, OR, NOT can be combined with Boolean logic: AND, OR, NOT ORDER may be used to format results ORDER may be used to format results

UPDATE tables SET item = expression SET item = expression WHERE expression WHERE expression INSERT INTO table INSERT INTO table VALUES ….. VALUES …..