1 / 27

Database

Database. Week 6 Mid-term Review. 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%. Business Intelligence.

azia
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 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)

More Related