1 / 25

CIS 218 Database Implementation

CIS 218 Database Implementation. Three Stages of Database Development. Requirements Design Implementation. MySQL. An open-source DBMS Can be accessed via: the command line phpMyAdmin a custom interface Popular for web applications. Using the Command Line. Launch WampServer

travis-bright
Download Presentation

CIS 218 Database Implementation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CIS 218Database Implementation

  2. Three Stages ofDatabase Development • Requirements • Design • Implementation

  3. MySQL • An open-source DBMS • Can be accessed via: • the command line • phpMyAdmin • a custom interface • Popular for web applications

  4. Using the Command Line • Launch WampServer • Open the MySQL console • Press Enter at the password prompt

  5. Creating a Database • CREATE DATABASE dbname; > CREATE DATABASE hcc; • USE dbname; > USE hcc;

  6. Creating a Table • CREATE TABLE tblname( column_specs ); CREATE TABLE hccStudent( sid CHAR(11) NOT NULL,stuLast VARCHAR(20) NOT NULL,stuFirst VARCHAR(20) NOT NULL, birthdate DATE NULL, PRIMARY KEY (sid)) ENGINE = InnoDB;

  7. IF NOT EXISTS • CREATE TABLE IF NOT EXISTS tblname( column_specs ); • Checks to see if the table already exists, and only creates it if it does not • Prevents errors for tables that have already been created

  8. Storage Engines • MySQL supports multiple storage engines • Engine determines certain table characteristics • InnoDB • Supports referential integrity • Supports cascade updates and deletes • Supports transactions • Supports row-level locking • SHOW ENGINES; displays available engines • Default engine is InnoDB

  9. MySQL Data Types • CHAR – fixed length string, 0-255 • VARCHAR – variable string, 0-255 • TEXT – strings up to 65535 characters • BLOB – binary data, e.g. images • INT – whole numbers • DECIMAL – floating point numbers • DATE – YYYY-MM-DD • DATETIME – YYYY-MM-DD HH:MM:SS

  10. Naming Rules • Identifiers are the names of databases, tables, and columns • Identifiers may contain any alphanumeric character plus ‘_’ or ‘$’ • Identifiers can start with any character that is legal in an identifier • An identifier may not consist entirely of digits

  11. Naming Rules • Table and database names can be quoted (delimited) with backticks ( ` ) • CREATE TABLE `Employees` • Required only when identifiers contain spaces, which should be avoided • CREATE TABLE `Full Time Employees` • Period (.) and slashes (/, \) are never allowed • Identifiers can be up to 64 characters long

  12. Inserting & Selecting Data • INSERT INTO tblname VALUES (values); • INSERT INTO hccStudent VALUES ('123-45-6789', 'Simpson', 'Homer', '1947-5-5'); • INSERT INTO hccStudent VALUES ('456-78-9809', 'Simpson', 'Bart', NULL); • INSERT INTO hccStudent VALUES ('555-66-7890', 'Bird', 'Tweety', '10-10-2013'); • SELECT * FROM hccStudent; Invalid date format; what happens?

  13. Alternative Syntax • INSERT INTO tblname (columns) VALUES (values); • Less dependent on database structure INSERT INTO hccStudent (sid, stuLast, stuFirst) VALUES ('123-45-6789', 'Simpson', 'Homer'); Note that birthdate is optional. What happens if you try to omit stuFirst?

  14. Useful Statements • DESCRIBE tblname; • or DESC or EXPLAIN • SHOW TABLES; • SHOW CREATE TABLE tblname; • SHOW DATABASES; • source filename; • DROP TABLE [IF EXISTS] tblname; • DROP DATABASE dbname;

  15. Auto Increment • Used to automatically increment a PK • Surrogate key • Has no meaning to the user • CREATE TABLE hccAdvisor (advisorID INT NOT NULL AUTO_INCREMENT,advisorFirst VARCHAR(30) NOT NULL, • advisorLast VARCHAR(30) NOT NULL, • advisorExt VARCHAR(4), • PRIMARY KEY (advisorID) • )ENGINE=InnoDB;

  16. hccAdvisor advisorID advisorFirst advisorLast advisorExt Inserting Rows • Use NULL in place of a value for surrogate keys (AUTO_INCREMENT) INSERT INTO hccAdvisorVALUES (NULL, 'Tina', 'Ostrander', '6040'); • List columns, but leave out primary key INSERT INTO hccAdvisor (advisorFirst, advisorLast, advisorExt) VALUES ('Tina', 'Ostrander', '6040');

  17. Foreign Keys • A Student can have more than one Score hccStudent sid stuLast studFirst birthdate hccScore scoreID score type comment sid • CREATE TABLE hccScore (scoreID INT NOT NULL AUTO_INCREMENT, score DECIMAL(4, 2), • type CHAR(1), • comment TEXT, • sid CHAR(11), • PRIMARY KEY (scoreID), • FOREIGN KEY (sid) REFERENCES hccStudent(sid) • )ENGINE=InnoDB;

  18. Referential Integrity • Every foreign key value must have a matching primary key value or else be null. INSERT INTO hccScoreVALUES (NULL, 9.5, 'Q', '123-45-6789', NULL); INSERT INTO hccScoreVALUES (NULL, 88.0, 'T', '333-44-5555', NULL); hccScore scoreID score type sid comment This value is NOT in the parent table!

  19. Minimum Cardinality • If a parent entity is optional (minimum = 0), then the foreign key column should be optional hccAdvisor advisorID advLast advFirst advEmail hccStudent sid stuLast studFirst birthdate advisorID Foreign key is optional(NULL)

  20. Minimum Cardinality • If a parent entity is required (minimum = 1), then the foreign key column should be required hccAdvisor advisorID advLast advFirst advEmail hccStudent sid stuLast studFirst birthdate advisorID Foreign key is required(NOT NULL)

  21. Escaping Quotes When a string value contains a quote, it needs to be escapedINSERT INTO hccScoreVALUES (NULL, 97, 'P', '880359176', 'This was John\'s best paper.');

  22. Practice • Create a “ratings” table to store restaurant ratings • Make the primary key auto-increment • Make the restaurant name required ratings ratingID INT name VARCHAR(30) address VARCHAR(50) city VARCHAR(30) price VARCHAR(30) rating INT(1) comments TEXT reviewDate DATE

  23. CREATE TABLE ratings( ratingID INT NOT NULL AUTO_INCREMENT, name VARCHAR(30) NOT NULL, address VARCHAR(50) NULL, city VARCHAR(30) NULL, price VARCHAR(30) NULL, rating INT(1) NULL, comments TEXT, reviewDate DATE, PRIMARY KEY (ratingID) )ENGINE=InnoDB;

  24. Practice • Populate the “ratings” table Lakeside 123 Westlake Seattle Price: High Rating: 5 Comments: Good food, great service 1-5-2010 Trapper’s 97 Wax Rd. Covington Price: Medium Rating: 5 Comments: Best sushi EVER! 3-10-2010 Applebee’s 3432 104th Kent Price: Medium Rating: 1 Comments: Bleck! 4-19-2010

  25. INSERT INTO ratings VALUES(NULL, 'Lakeside', '123 Westlake', 'Seattle', 'High', 5, 'Good food, great service', '2010-01-05'); INSERT INTO ratings VALUES(NULL, 'Trapper\'s', '97 Wax Rd.', 'Covington', 'Medium', 5, 'Best sushi EVER!', '2010-03-10'); INSERT INTO ratings VALUES(NULL, 'Applebee\'s', '3432 104th', 'Kent', 'Medium', 5, 'Bleck!', '2010-04-19');

More Related