Databases and DBMS

1. Databases and DBMS

2. Database - I • A better way to manage & organise data ?? • Better than what? • A collection of data stored in files. • Each file contains data that is related in some way - name, address, ID number, etc.

3. Database - II • Files may also be related - at a higher level, e.g. they all relate to information inside the University. • A database management system (dbms) can be used to help organise data in one place. • Access, FoxPro, Paradox, Oracle, dBase, ...

4. Why Use a DB - I? • Reduce data redundancy • Data exists in only one place • Improve data integrity • cf. transaction/master files • no duplication • Integrate files • Data from different files can be integrated/joined

5. Why Use a DB - II? • Improve data security • Security at different levels - • field, file, database • Access control can vary - • read only, update, no access at all • Reduce development time • Existing files can be updated, new fields added • Data independence from system

6. Relational Databases - I • Data is organised in flat files called tables or relations • Each table consists of: • rows (tuples) • fields (attributes) • Domain - the range of values that an attribute can have • A key attribute - identifier • Primary Key

7. 2482 2845 3432 3691 Linda John Robert Ron Lai Chan Lee Kwok 1801 Waterloo Road 261 Tat Chee Avenue 118 Tolo Place 2101 Sunshine City 24371986 45291107 62231811 86190010 STUDENT MASTER TABLE Domains Table (Relation) Fields (Attributes) Rows (Tuples)

8. Relational Databases - II • Most efficient type of database because of its flexibility - DB can be adapted over time. • Easy to • join tables to create another view of data • add new data • redefine data structure • create new fields, modify field structures, delete fields

9. Database Management System (DBMS) • A set of computer programs that control the creation, maintenance and use of the database of an organisation & its end users • Data management is different • MS Access, Dbase, Oracle

10. Using a RDBMS • Creating the structure of the db • A table for each thing (entity) • Each attribute of the thing becomes a field • Define data types and field sizes • Define a Primary Key • Entering the records (data) • Obtain your information from the data … queries

11. Querying the db - I • A query is an action that enables the user to retrieve information from the database in a report (view) based on some criteria. • Three types of query: • select relational operation • project relational operation • join relational operation

12. Querying the db - II • Select • Selecting certain records, e.g. ID numbers starting with the letter A • Project • Specifying that only certain fields appear in the query output, e.g. names of customers who have bought life insurance policies of more than $200,000.

13. Querying the db - III • Join • Two or more tables can be joined together • Data can then be extracted from both tables and combined into integrated information • A pair of tables must have a common field in order to join them

14. Query: Display customer name and quantity order for all sales orders for Part C-143 SALES ORDERS CUSTOMERS SALES ORDER NO. CUSTOMER NUMBER CUSTOMER NUMBER PART NO. QUANTITY ORDERED NAME ADDRESS PHONE 1421 1100 M-200 100 1422 2600 C-143 15 1423 1425 A-101 65 1424 2201 C-143 1000 1425 1087 B-231 4 1426 2890 B-388 140 1087 L.Lai Shatin 436-8800 1100 R.Lee Mong Kok 619-2200 1425 W.Wong Central 232-0108 2201 R.Kwok North Point 457-7030 2600 P.Poon Tai Po 976-2400 2890 K.Chan Mid Level 629-9021 SELECT: PART C-143 PROJECT: CUSTOMER NAME JOIN: BY CUSTOMER NUMBER SALES ORDER CUSTOMER NUMBER CUSTOMER NAME PART# QUANTITY ORDERED 1422 2600 K.Kwok C-143 15 1424 2201 P.Poon C-143 1000 Response to Query (view)

15. Issues • Personnel databases (e.g. at CityU) • What kind of information is un/reasonable to keep? • What kind of information is un/necessary to keep? • File management system or DBMS - dis/advantages?

16. = $$$ Databases: Value and Application Adapted from CW (1997)

17. Overview • Short Story • Capabilities of Databases • Applications • Why Doesn’t Everyone Use Databases (the right way?) • Database design • Database manipulation and retrieval

18. A Short Story “Hello Dr. Davison. My name is Fanny. I am calling from NTT to introduce myself as your personal account representative. … Please let me know if there is anything I can do for you concerning your international direct dialing needs. …”

19. Questions • Does every NTT customer get this phone call? • If not, why did I get the call? • How did NTT know whom to call? • What are the costs/benefits?

20. Golden Rule of Business: ABC #1 Customers. 10% account for 60...70% of the business revenue or profit. #2 Customers. 30% account for 20…30% of the business bottom line. #3 Customers. 60% account for 10…20% of the business bottom line.

21. Database Roles/Functions • Memory of the Organisation. Helps the organisation remember what people forget. • Any-time facility. Allows people to store information at one time for use at a later time. • Data warehouse. A data inventory which can be mined for future knowledge creation.

22. Organisational Memory Who is the customer? What are the customer’s special characteristics? Did the customer contact us before about a particular problem? What happened? Who handled the situation? Cross-selling. Does the customer have additional needs that another division of our company can satisfy?

23. Any-time facility Global (international) companies "working around the clock". Different teams in different time zones working together on a joint project. Need to leave messages, business results, instructions, … for later use. (E-mail, voice mail).

24. Data Warehousing and Mining Finding hidden meaning in the data, e.g.: IKEA: "People who smoke also buy more furniture" Hang Seng Bank: “Men between 25 and 34 with a family and living in Yuen Long keep a balance on their credit card (and therefore have to pay interest)" Giordano: "Green T-shirts are selling at twice the speed of red T-shirts"

25. Why Don’t All Firms Use DBs? Many companies keep files, but not in an integrated database. Did you ever receive two identical letters from the same company? Or from CityU? Does the bank where you keep your savings account know whether you have a credit card? Do you sometimes have to provide the same information to FB and your Dept?