Module 2 Data & Database Management Systems

Database

A shared, integrated computer structure that houses a collection of end-user data and metadata about the stored data, such as the data types of the data

The WHERE condition in a SELECT statement

Condition can be something like: Comparing the value of a field to a different value or constant Checking to see if the value is within a certain range, Checking to see if the value matches a certain pattern Checking to see if the value is one of a set of values Checking to see if a field is empty (in other words, is its value = NULL)

DBMS

Create database Create tables Create supporting structures (indexes) Read database data Modify database data (insert, update, delete) Maintain database structures Enforce rules Control concurrency Provide security Perform backup and recovery

Components of SQL

DDL (data definition language) DDL commands are used to create and modify the tables and other objects in a database. The DDL commands are CREATE, ALTER, and DROP. DML (data manipulation language) DML is concerned with the retrieval and update of data in the database. The DML commands are SELECT, INSERT, UPDATE, and DELETE. DCL (data control language) DCL is used to specify who can access data in the database and what operations they can perform. The DCL commands are GRANT and REVOKE.

Prominent DBMS Products

Microsoft Access Microsoft SQL Server 2016 & 2017 versions (2012 & 2014 still around in businesses) Oracle Corporation Oracle Database 11g (and now 12c) MySQL IBM DB2 MongoDB and NoSQL - Newer DBMS trending up in popularity

Foreign Keys

Sets up a connection, or relationship between tables.

Data types

Tells the DBMS what type of data to expect in an attribute Stored with table structure in the system catalog Many data types available in a DBMS Data types can vary from DBMS to DBMS Some general data types available in most DBMS: Integer Char (n) where n is the fixed number of characters allowed Varchar (n) where n is the max number of characters Date

ALTER TABLE command

The ALTER TABLE statement is a DDL command in SQL similar to the CREATE TABLE statement. It can be used to modify a column, add a column, delete a column or add any type of CONSTRAINT such as one for a primary key or foreign key. Can use ADD, MODIFY, or DROP. EX) ALTER TABLE PERSON MODIFY Name VARCHAR(25); changed the number of characters allowed in Name from 20 to 25

The SELECT command

The SELECT command forms the basis for database queries. Primarily used To retrieve data from the database. Also used when creating a copy of a table or creating views Can be used to specify rows for updating.

Form of SELECT

The basic form of the SELECT command is: SELECT field-list FROM table-list WHERE field-expression GROUP BY group-fields HAVING group-expression ORDER BY field-list (The WHERE, GROUP BY, HAVING, and ORDER BY clauses are optional.)

Relational Database Model

The dominant database model is the relational database model—all current major DBMS products are based on it. It was created by IBM engineer E. F. Codd in 1970. It was based on mathematics called relational algebra. In this module we examine and explain the relational database model.

Structured Query Language (SQL)

is an internationally recognized standard database language that is used by all commercial DBMSs. Database language must allow Creation of database and table structures Perform basic data management chores such as adding, deleting, and modifying data Perform complex queries and transform raw data into useful information.

Database Management System (DBMS)

software that manages the data and database structure and controls access to the data stored in the database

Information

data that is processed, organized, structured, or presented in a given context so as to make it useful

JOIN

A JOIN phrase connects tables on a common column, in this case CityNumber. When we issued the CREATE TABLE commands for our two tables, we defined CityNumber as a foreign key in PERSON, and as the primary key in CITY. To JOIN the tables, we need to modify the SELECT command to request columns from both tables. Because some field names are the same in both tables, such as City, we prefix the field name with the table name, as in CITY.City. This is required by the DBMS. The command now looks like this: SELECT PERSON.Name, PERSON.Address, CITY.City, CITY.State_code FROM CITY JOIN PERSON on PERSON.CityNumber = CITY.CityNumber;

The database

A database is a self-describing collection of integrated tables. The tables are called integrated because they store data about the relationships between the rows of data. A database is called self-describing because it stores a description of itself. The self-describing data is called metadata, which is data about data. Metadata is stored in the system catalog.

Characteristic of databases

Data is stored in tables, which have rows and columns like a spreadsheet. A database has a table for each type of entity customer, products, warehouses, etc. Each column stores values for an attribute of the entity, name, address, telephone number, etc. Each row in a table stores data about an occurrence or instance of the entity of interest i.e. one customer, one product, etc. A database stores data and relationships among tables.

WHERE example

Determine all cities in the state of MA SELECT * FROM CITY WHERE State_code = 'MA'; Result from DBMS: CityNumber City State_code 4 Cambridge MA 5 Boston MA

The UPDATE command

For example, suppose Frank had a birthday since the data was entered, and we wanted to change his age in the table. We would write the UPDATE command like this: UPDATE PERSONSET Age = 20WHERE Name = 'Frank';

Example SELECT statement

For instance, to look at the values of all the fields in the CITY table, we would say: SELECT CityNumber, City, State_code FROM CITY; and the DBMS will display a table like this: City Number City State_code 1 Milwaukee WI 2 Houston TX 3 Orlando FL 4 Cambridge MA 5 Boston MA

Basic data types most common to DBMSs

INTEGER (a non decimal number) CHAR (enter the number of characters you know your field will hold-fixed length string of characters) VARCHAR(enter the max number of characters your field will hold, including any type of character, like CS200)

Example querying from a table using SELECT

If we want to query the PERSON table, our SELECT statement could look like this: SELECT IDNumber, Name, Height, Age, Address, CityNumber FROM PERSON; Or we could use a short-cut and use this format instead: SELECT * from PERSON;

The DELETE command

If we wanted to delete all records in the PERSON table where the Name is ꞌFrank', we could use this statement: DELETE FROM PERSONWHERE Name = ꞌFrank'; Keep in mind this command deletes the entire record (row) for all the records that meet the WHERE condition. The structure of the table as well as the other records that don't meet the WHERE condition remain intact in the database. As with the DROP TABLE command, extreme care should be taken when issuing a DELETE command against the database. Often times, the only way to recover deleted data is through database recovery activities which are both complex and time consuming.

Data

Raw facts, lack full meaning

Primary keys

Rows need to be uniquely identified within a table An attribute (or attributes) whose data value will be unique to a row is called the primary key

Inserting data into the table

The next step is to add data to our tables. We do this with a SQL command called INSERT. The command format is: INSERT INTO Table_name(column_names) VALUES (data values);

CREATE TABLE

The syntax (grammar rules) of the CREATE TABLE command is: CREATE TABLE Table_name(column_specifications); To create the CITY table the CREATE TABLE command would be: CREATE TABLE CITY (CityNumber INTEGER,City VARCHAR(20),State_code VARCHAR(2),CONSTRAINT City_PK PRIMARY KEY (CityNumber));