Database

1. Database Week 6 Mid-term Review Fox MIS Spring 2011

2. Format of Mid-term Exam • TIME: 11:00 am -12:00 • LOCATION: SP114 • FORMAT: • Multiple Choices (10-15 questions) • Q&A (4-6 questions) • ERD (1 question) • Content ratio (approximately): BI 10-15%, ERD 30-35%, SQL 50-60%

3. Business Intelligence • What is Business Intelligence (BI)? • What’s the main role of BI in business • List some technologies supporting BI. • List BI skill and knowledge clusters. • What is BI maturity? • What are the four stages of the BI maturity model? • Explain why BI is an architecture and a collection of integrated operational as well as decision-support applications and databases that provide the business community easy access to business data.

4. Mental Map of BI

5. Database Design • Database Models, advantages & disadvantages • The uniqueness of relational model • The role of DBMS, DBMS functions

6. ERD • Entities • Attribute • Primary key, foreign key • Relationships (1:M, M:1, M:N) • Referential integrity

7. Drawing ERD • Steps: • Identify entities • Insert primary keys • Insert attributes (independence) • Identify relationships • Add bridge entity to simplify M:N relationships • Add foreign keys and build relationships Tips: for transactional database, it’s common to connect all “physical” entities to the core transactional entity/table.

8. Frequent Made Mistakes • Add wrong attributes • Add record as attributes (e.g. laptop or product name) • Forgot to identity relationships • Couldn’t identify M:N relationships • Wrong relationships (e.g. customer<->store) • Forgot referential integrity • Loops

9. First Invoice

10. 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

11. 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

12. SQL • Database Manipulation Language (DML)SHOW TABLES, DESCRIBESELECT, *, WHERE, ORDER BYAND & ORCOUNT, DISTINCTDELETE, INSERT, UPDATEJOIN, ALIAS • Database Definition Language (DDL)CREATE TABLE/DATABASEDROP TABLE/DATABASEALTER, PRIMARY KEY, FOREIGN KEY

13. SELECT, COUNT, DISTINCT, WHERE • Table: customer_list, Databse: Sakila • Where does Judy Gray live? • SELECT * • FROM CUSTOMER_LIST • WHERE NAME= 'JUDY GRAY‘ (or NAME LIKE ‘JUDY GRAY’) • How many countries do our customers live in? • SELECT COUNT(DISTINCT COUNTRY) • FROM CUSTOMER_LIST • How many customers live in Egypt? • SELECT COUNT(ID) • FROM CUSTOMER_LIST • WHERE COUNTRY = 'EGYPT' • What cities in Egypt do our customers live in? • SELECT DISTINCT CITY • FROM CUSTOMER_LIST • WHERE COUNTRY = 'EGYPT' • How many customers live outside of the United States? • SELECT COUNT(ID) • FROM CUSTOMER_LIST • WHERE COUNTRY <> 'UNITED STATES'

14. LIKE, AND, ORDER BY • Table: film • How many films are less than or equal to 90 minutes in their lengths? • SELECT COUNT(FILM_ID) • FROM FILM • WHERE LENGTH <= 90 • How many films are about astronauts? • SELECT COUNT(FILM_ID) • FROM FILM • WHERE DESCRIPTION LIKE '%ASTRONAUT%' • List of the movie titles which are about astronauts and their length are less than 90 minutes • SELECT TITLE • FROM FILM • WHERE (DESCRIPTION LIKE '%ASTRONAUT%') AND (LENGTH < 90) • List of the movie titles and their length in descending order according to the length • SELECT TITLE, LENGTH • FROM FILM • ORDER BY LENGTH DESC

15. INSERT, UPDATE, DELETE • Table: retail_sales_feb • Put yourself as the customer • You bought it from Sales_rep whose name is Smith at Feb.11.2011 • Your order_no is 34567 and cust_no is 3456 DESCRIBE RETAIL_SALES_TAB (you might need this statement to see data type for each field) INSERT INTO RETAIL_SALES_FEB VALUES ('2011-2-11', 34567, 3456, ‘Yang Yang', ‘Smith', 'CB03', 'Bike', 'Sport', 1, '$250', '$250') • Put person next to you as the customer • You don’t know anything about that person except the name and intend to fill the rest of the fields later on • Note: you need to figure out what the key is and make it up DESCRIBE RETAIL_SALES_TAB (Primary key should be “NO” in Null field) INSERT INTO RETAIL_SALES_FEB (ORDER_NO, CUSTOMER) VALUES (45678, 'Joe Smith') • You found out that the person you bought from was not Min but Smith. Correct the information UPDATE RETAIL_SALES_FEB SET SALES_REP = 'Smith' WHERE ORDER_NO = 34567 • You want to cancel your order and make it as if it never happened DELETE FROM RETAIL_SAELS_FEB WHERE ORDER_NO = 34567

16. Join • Output customer names, payment amount, CSR name, and rental date from database sakila: • SELECT rental.rental_date, customer.first_name, customer.last_name, payment.amount, staff_list.nameFROM rental, customer, payment, staff_listWHERE rental.rental_id=payment.rental_id AND rental.customer_id=customer.customer_id AND rental.staff_id=staff_list.ID • You can also usetable1 INNER JOIN table2 ON conditions • In above query, how many tables are joined? Why and when are these tables joined? How are these table joined?

17. Alias • Just give another name for the output values • SELECT amount FROM payment AS p • SELECT amount, amount+1 FROM payment • SELECT amount AS "original_amount", amount+1 AS "new_amount" FROM payment

18. 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))

19. 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

20. 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))

21. 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')

22. 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

23. 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

24. 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

25. MySQL Data Types (Text Types)

26. MySQL Data Types (Number Types)

27. MySQL Data Types (Data Types)