1 / 18

Database

Database. Week 6 ERD and SQL Exercise. CREATE DB and TABLE. Create a database: CREATE DATABASE database_name Example: CREATE DATABASE my_db Create a table in a database: CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, .... ) Example:

signa
Download Presentation

Database

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. Database Week 6 ERD and SQL Exercise Fox MIS Spring 2011

  2. CREATE DB and TABLE • Create a database: CREATE DATABASE database_nameExample: CREATE DATABASE my_db • Create a table in a database: CREATE TABLE table_name(column_name1 data_type,column_name2 data_type,column_name3 data_type,....) Example: CREATE TABLE Persons(P_Id int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255))

  3. SQL Constraints • Constraints are used to limit the type of data that can go into a table. • Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement). • NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK • DEFAULT

  4. CHECK • The CHECK constraint specifies that the column "P_Id" must only include integers greater than 0. • CREATE TABLE Persons(P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),CHECK (P_Id>0))

  5. DEFAULT • The DEFAULT constraint is used to insert a default value into a column. • CREATE TABLE Persons(P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255) DEFAULT 'Sandnes')

  6. PRIMARY KEY • The PRIMARY KEY constraint uniquely identifies each record in a database table. • Primary keys must contain unique values. • A primary key column cannot contain NULL values. • Each table can have only ONE primary key. • CREATE TABLE Persons(P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),PRIMARY KEY (P_Id)) • ALTER TABLE PersonsADD PRIMARY KEY (P_Id) • Creates primary key constraint for P_id column • Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created). • ALTER TABLE PersonsDROP PRIMARY KEY • Drops a PRIMARY KEY constraint

  7. FOREIGN KEY • A FOREIGN KEY in one table points to a PRIMARY KEY in another table. • CREATE TABLE Orders(O_Id int NOT NULL,OrderNo int NOT NULL,P_Id int,PRIMARY KEY (O_Id),FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)) • ALTER TABLE OrdersADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) • Create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created • ALTER TABLE OrdersDROP FOREIGN KEY P_Id • Drops a FOREIGN KEY constraint

  8. ALTER TABLE • The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. • ALTER TABLE table_nameADD column_name datatype constraint(optional) • ALTER TABLE table_nameCHANGE OLD_COLUMN_NAME NEW_COLUMN_NAME datatype constraint(optional) • Old column name and new column name can be the same. • ALTER TABLE table_nameDROP COLUMN column_name • ALTER TABLE PersonsADD DateOfBirth date • ALTER TABLE PersonsDROP COLUMN DateOfBirth • ALTER TABLE PersonsCHANGE DateOfBirth DateOfBirth year

  9. Exercise • Create database MISxxxearth • Create table ‘MySpring2011’ which has your course enrollment information Spring 2011 • Use { } instead of ( ) • Course ID, Course No, Course Name, Credit, Instructor Name, Day, Time • Use as many constraints as you can • Each constraint is supposed to be correct one in a logical sense • Values in some columns are supposed to be unique • There should be a primary key • Credit should be greater than zero • Add one more column ‘Department’ • Department is supposed to be ‘MIS’ by default • Insert your course information in the table • Useful command: DESCRIBE table_name

  10. ERD – Example

  11. One Possible ERD

  12. Exercise • Create database MISxxxsaturn. • Create tables for our invoice ERD example • Sample answer is in the previous slide • Decide which data type is assigned for each column • Put necessary constraints into columns • Make necessary primary and foreign keys • Insert data of three invoices (next three slides) into corresponding tables • Add yourself as a customer in the customer table • Add a person next to you as a seller in the seller table • Add your favorite book information in the product table

  13. First Invoice

  14. Second Invoice 3212 Jason Mraz 72 Spring Street New York, NY 10012 234567 6/10/2008 3930722 The Big Short Economics 20.00 1 20.00 141414 Databases R Amazing IS 100.00 2 200.00 TOTAL 220.00 Tax 13.20 Grand Total 233.20

  15. Third Invoice 3213 6/4/2008 Sunny California 610 W. Ash St San Diego , CA 92101 6/12/2008 455550 To the End of the Land Novel 20.00 5 100.00 141414 Databases R Amazing IS 100.00 1 100.00 TOTAL 200.00 Tax 12.00 Grand Total 212.00

  16. MySQL Data Types (Text Types)

  17. MySQL Data Types (Number Types)

  18. MySQL Data Types (Data Types)

More Related