Difference between revisions of "CSC220 Database Management Systems"
(→Important Concepts) |
|||
Line 9: | Line 9: | ||
* DBMS = database management system | * DBMS = database management system | ||
+ | |||
+ | <br /> | ||
+ | <br /> | ||
+ | <br /> | ||
+ | <br /> | ||
+ | <br /> | ||
;Definition | ;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. | : 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. | ||
+ | <br /> | ||
+ | <br /> | ||
+ | <br /> | ||
* 1970: seminal paper by Ted Codd, where proposition is that DBMS users should see the data as '''tables''', called '''relations''' | * 1970: seminal paper by Ted Codd, where proposition is that DBMS users should see the data as '''tables''', called '''relations''' |
Revision as of 08:41, 6 October 2010
--D. Thiebaut 12:08, 6 October 2010 (UTC)
Contents
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:
- Satellite data
- Social Networks (Facebook)
- YouTube videos
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');
- 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') );