1 / 21

Introduction to Database

Introduction to Database. Lei Yang Computer Science Department. An example. Why we use database?. You have a company with more than 10000 employees… Someday, you want to find out The average salary of employees who own Ph.d degree… The average age of your company… …. Manually?. No!.

lola
Télécharger la présentation

Introduction to 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. Introduction to Database Lei Yang Computer Science Department

  2. An example Why we use database? • You have a company with more than 10000 employees… • Someday, you want to find out • The average salary of employees who own Ph.d degree… • The average age of your company… • … Manually? No!

  3. Manage Employees in the Company • Employee Information • Name: Jack, Ram… • Gender: Male, Female • Degree: Bachelor, Master, Ph.d… • Department: HR, Market Department… • Job: Engineer, Salesman, … • Salary: 10k, 15k, … • Manager: Jason, … • … Problem: How to store these data?

  4. Table • Database Store Data in Tables COLUMN A field or an attribute ROW: a record Text Currency NUMBER Question: how to distinguish two employee with nearly the same information?

  5. Primary Key Primary Key Unique

  6. Data Type - 1 • Text • Stores text, numbers, or a combination of both, up to 255 characters long. Text fields are the most common of all data types. • Memo • Stores long text entries up to 64,000 characters long. • Number: int, float… • Date/Time • Stores dates, times, or both. • Currency • Stores numbers and symbols that represent money.

  7. Data Type - 2 • AutoNumber • Automatically fills in a unique number for each record. AutoNumber is used as primary key in many tables. • Yes/No • Stores only one of two values, such as Yes or No, True or False, etc. • OLE Object • Stores objects created in other programs such as a graphic, Excel spreadsheet, or Word document. • Hyperlink • Stores clickable links to files on your computer, on the network, or to Web pages on the Internet.

  8. Data Type - 3 • Lookup Wizard • A wizard that helps you create a field whose values are selected from a table, query, or a preset list of values.

  9. Query Answer in Database-1 • Query: Find out the names of all employees whose salary is greater than 12k?

  10. Query in Database-2 SELECTION PROJECTION James, Mary

  11. Query in Database-3 • SELECTION • Selecting records which satisfies certain conditions • Projection • Projecting a field into query simply means including it in the query

  12. Query in Database-4 • SQL(Structured Query Language) • Used in Database • A simple structure • SELECT <fields> • FROM <table> • WHERE <condition> An example show how it works

  13. Query in Database-4 • Query: Find out the names of all employees whose salary is greater than 12k? • SELECT • FROM • WHERE PROJECTION Name <field> <table> Employee Salary > 12k <condition> SELECTION • SELECTION: Selecting records which satisfies certain conditions. • PROJECTION: Projecting a field into query simply means including it in the query

  14. Query in Database-5 • How to search in two tables? Find out the name of employees in HR?

  15. Query in Database-6 Department Employee • Cartesian Product X =

  16. Query in Database-7 • SELECT <Name> • FROM <Employee, Department> • WHERE <DepartmentName = “HR” and • Employee.DptID = Department.DptID> JOIN Cartesian Product

  17. Query in Database-8 • Operators in conditions

  18. Query in Database-9 • Aggregate Function How many employees are there in department D004?

  19. Query in Database-10 • SELECT count(EmployeeID) • FROM <Employee> • HAVING DptID = “D004” Aggregate Function

  20. Query in Database-11 • Aggregate Functions

  21. Question

More Related