CSC220 Database Management Systems

--D. Thiebaut 12:08, 6 October 2010 (UTC)

---

Important Concepts

• The information here is taken from Chapter 1 (required reading) of Garcia-Molina, Ullman and Widom's Database Systems: the Complete Book. Their Chapter 2, which is also online, is great reading.

• DBMS = database management system

Definition

A DBMS is a tool for creating and managing large amounts of data efficiently and allowing it to persist over long periods of time, safely.

• 1970: seminal paper by Ted Codd, where proposition is that DBMS users should see the data as tables, called relations

• ==> Relational database systems

• SQL = Structured Query Language

• 1990: relational database systems are the norm

• today: DBMS have evolved to new forms (cloud).

• example of large databases:
  • Google
  • Satellite data
  • Social Networks (Facebook)
  • YouTube videos

• Two kinds of users of a DBMS: Admin and Users
  • Admin create/modify organization of the data (table size, data size, constraints on the data values) = Schema of the database.
  • Users query the DB to lookup, search, insert new data, or delete new data. The result (SQL) is presented in the form of temporary tables.

Concepts of Transcation and Query

A transaction is a group of queries (possibly just one) that should operate atomically and in isolation from one another. A complete transaction should be durable. This means that logging and recovery processes are an integral part of a DBMS.

• DBMS = { data, metadata, logs, statistics, indexes + data-structures }

Example

• Users, purchasing items from a store.
• 3 tables: users, orders, items

Create Tables

CREATE TABLE `users` (
 `userId` INT(1) NOT NULL AUTO_INCREMENT,
 `userName` VARCHAR(100) DEFAULT NULL,
 PRIMARY KEY  (`userId`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;


CREATE TABLE `orders` (
 `orderId` INT(1) NOT NULL AUTO_INCREMENT,
 `userId` INT(1) DEFAULT NULL,
 `itemId` INT(1) DEFAULT NULL,
 PRIMARY KEY  (`orderId`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;


CREATE TABLE `items` (
 `itemId` INT(1) NOT NULL AUTO_INCREMENT,
 `itemName` VARCHAR(100) DEFAULT NULL,
 `price` FLOAT DEFAULT NULL,
 PRIMARY KEY  (`itemId`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

Insert Data

• Add 5 users to the database (Marie, John, Manon, Rob, Elaine)

INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Marie');
INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'John');
INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Manon');
INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Rob');
INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Elaine');

• Add 6 items and their price (pencil, eraser, paper, ink, ruler, markers)

INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'pencil','1');
INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'erase','0.5');
UPDATE `items` SET `itemId`='2',`itemName`='eraser',`price`='0.5' WHERE `itemId`='2';
INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'paper','5');
INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'ink','30');
INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'ruler','1.5');
INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'markers','2.5');

• Create some user orders of items. e.g. Marie buys a pencil, an eraser, paper, and ink.

INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'1','1');
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'1','2');
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'1','3');
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'1','4');
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'2','1');
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'3','1');
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'3','4');
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'5','4');
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'5','3');

Table-related operations

• to delete a table: "DROP TABLE"
• to erase a table without deleting it: "TRUNCATE TABLE"

Some queries

SELECT * FROM `items`;

SELECT `itemName` FROM `items`;

SELECT `itemName` FROM `items`WHERE `itemId`=2;

SELECT * FROM `users` ORDER BY `userName` ASC;

SELECT * FROM `orders` WHERE `userId`=1;

SELECT `itemName` FROM `items` WHERE `itemId` IN ( 1, 2, 3 );

SELECT `itemName` FROM `items` WHERE `itemId` IN ( SELECT `itemId` FROM `orders` WHERE `userId`=1 );

SELECT `itemName` FROM `items` WHERE `itemId` IN ( 
            SELECT `itemId` FROM `orders` WHERE `userId` IN  ( 
                         SELECT `userId` FROM `users` WHERE `userName`='Marie') );