1 / 40

Database Management

Chapter 4. Database Management. Employee record1. Employee record2. Employee record3. Employee record4. Name SSN Salary. Name SSN Salary. Name SSN Salary. Name SSN Salary. Data Elements. Employee database. Payroll file. Remunerations. Text: O’Brien : p. 144.

bardia
Download Presentation

Database Management

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. Chapter 4 Database Management

  2. Employee record1 Employee record2 Employee record3 Employee record4 Name SSN Salary Name SSN Salary Name SSN Salary Name SSN Salary Data Elements Employee database Payroll file Remunerations Text: O’Brien : p. 144

  3. Logical Data Elements • Field • represents an attribute of an entity • Record • Set of attributes that describe an entity • File • set of records of the same type • transaction file, archive file, ... • Database • integrated set of logically related files and the rules they have to satisfy Text: O’Brien : p. 145

  4. Database Employee database Payroll data Person data Training data

  5. Database Management User Inquiry Applications Request Database Management System Corporate database Text: O’Brien : p. 146

  6. Example CARS (model, #cylinders, origin, tax, fee) Model#cyl origin taxfee Origin  tax ------------------------------------------------------ #cyl  fee Rabit4Germany1530 Mustang6USA0 45 Mirafiori4Italy1830 Accord4Japan2030 Cutlass8USA060 Laguna 4 France 2030 BMW 6 Germany 15 45 Velsatis 6 France 20 45 Velsatis 4 France 20 30

  7. Example • Drivers having a driver-no , a name , a home address and a birth-date take out vehicles to make deliveries. A vehicle ( with a unique id-number ) may be taken out of a depot whenever available and kept out for any length of time . It is possible for a vehicle to be taken out more than once on a given day by any driver . • Each time a driver takes out a vehicle , he or she takes out a load made up of any quantity of any number of item types , identified by an item-number. Every time a vehicle is taken out , the driver can incur expenses of allowed type (e.g. fuel cost ). • Each expense type has a code-no. The amount and code-no are recorded each time an expense is incurred . • Any number of stops can be made during the trip . An address of the stop is recorded for each stop , together with the quantity left on that stop for each item type . A driver stops at an address only one during a trip . However, stops can be made at the same address on different trips.

  8. Assignment The university that has to keep track of student transcripts could use a database structured as follows. Student(Student Number - Last name - First name - SSSN - Street - Nr - Zip Code - City - State - Country - Phone Number - Birth Date - Gender - Class - Major - Minor) Department(Depart. Code - Name - Office Nr - Office Code) Course(Course Code - Course Name - Description - Credits - Department) Section(Course Code - Section Nr - Instructor - Semester - Year) Transcript(Student Number - Name - Course Code - Section Number - Grade)

  9. Work with DBMS-software Operating system DBMS Application programs • Database development • Database inquiry • Database maintenance • Application development Databases Data Dictionary Text: O’Brien : p. 147

  10. Dictionary Access Text: O’Brien : p. 149

  11. Database Inquiry • Query Languages • SQL Select Name, department, salary From Employee, Payroll Where Employee.number = Payroll.number and Function = “Analyst”. • QBE Employee Name number department  num  Payroll number function salary num “ Analyst” 

  12. DBMS User interface Query language Report generator Application generators Host-language Program Interface Data-dictionary programs DBMS utilities DBMS kernel program

  13. Problems with files • Data redundancy • Non-integrated files • Data dependency • Other problems • inconsistency • integrity Possible solutions : database management system • separation of files and applications • specific database languages • central dictionary

  14. Characteristics of databases Redundancy . storage cost . multiple update needed . inconsistency possible Data independence The degree on which data structures can be modified without affecting the related programs Solution : Database architecture with three levels ( conceptual, internal , external )

  15. Types of Databases • Operational databases (subject area databases SADB) • transaction databases, production databases • employee database, stock database, ... • Analytical databases ( management databases ) • data retrieved from external and operational databases • Accessible for OLAP, DSS, management information systems • Data Warehouses • contain historical data from various operational and external databases • central source for standardized and integrated data for management and users • data mart is a limited version for a specific subject • used for data mining Text: O’Brien : p. 150

  16. Types of Databases (2) • Distributed databases • mostly for local needs, can be copies • consistency problems • distributed over servers • related, eventually via WWW or intranet • Personal databases for end users • various data gathered by users on their workstations • eventual with spreadsheet or DBMS • External databases • often access charge • statistical , bibliographical Text: O’Brien : p. 151-153

  17. Types of Databases (3) External databases Client-PC or NC Distributed databases Network server Opera- tional databases End-user databases Data- warehouse Management databases Text: O’Brien : p. 151

  18. Hypermedia DB on the WEB Internet intranet extranet WEB- browser Web-server software Client-PC or NC Network server Hypermedia database parts of a Web-information system Text: O’Brien : p. 153

  19. Data Resource Management • Policy governing data ownership • and access control • Data Resource Requirement Planning • Data model and data architecture Data administration • Strategic and technical • database planning • Define applications • Procedures for data retention • Operational procedures Data Planning • Physical database • Logical database • Data dictionary • HW and SW Database Administration Text: O’Brien : p. 154

  20. Benefits and Limitations • Benefits of a DBMS • reduces data redundancy and integrates data • data accessible from all programs • programs not dependent on data format • query/answer and reporting facilities • easier application development and programming • better integrity and security • Limitations of a DBMS • increased complexity of the technology • development of large databases is difficult and expensive • slower than file management systems due to additional layer • more sensible for fraud, errors and failures

  21. Technical Principlesof database management

  22. Files Physical Logical A B C D E D C E A B

  23. Aspects of file usage File activity input 2000 orders processing 20% Client file 10000 records 100% revenue state 0.01% 1 instruction inquiry 1 inquiry Accessibility Evolution

  24. What is a database ? 1. A database is a set of data which are modifiable and accessible by a computer program. text, image, voice, structured information. 2. A database is a set of data which are . structured . integrated . non-redundant . shared It is a source of data for a large number of different applications and for a variety of users.

  25. Database type Employee Number Name City STUDIES CHILDREN First name Birthday Diploma year college 87 master math 94 econ 90 Accounting 94 college 91 Bache elec 94 occurrence 12 Duval Rome 16 Krols Dublin 38 Smets Berlin john 120694 inge 241198 lidy 070296 Carl 040894

  26. Sample file Supplier file SNUMBER SNAME STATUS CITY S1 De Smet 20 London S2 Janssens 10 Paris S3 Blanchart 30 Paris S4 Clark 20 London S5 Adams 30 Athens

  27. Supplier file with index on city SNUM LNAME STATUS City S1 De Smet 20 London S2 Janssens 10 Paris S3 Blanchart 30 Paris S4 Clark 20 London S5 Adams 30 Athens Supplier file City-index Athens . London . London . Paris . Paris .

  28. Index on city and index on status LNUM LNAME STATUS CITY S1 De Smet 20 London S2 Janssens 10 Paris S3 Blanchart 30 Paris S4 Clark 20 London S5 Adams 30 Athens Supplier file City-index Status-index Athens . London . London . Paris . Paris . . 10 . 20 . 20 . 30 . 30

  29. Non-dense index • SNUM SNAME STATUS CITY • S1 De Smet 20 London • S2 Janssens 10 Paris • S3 Blanchart 30 Paris • S4 Clark 20 London • S5 Adams 30 Athens Supplier file S2 . S4 . S5 . block 1 block 2 block 3

  30. File Organization: Indexed-sequential Bens Dooms Fagin parameters - index block size - data block size multi-level index blocks Ernest Fagin Adams Albert Bens Bodoo Claes Codd Dooms Ace Adams Behr Bens Ademar Aerts Albert Bodoo data blocks

  31. Fully Inverted file SNAME-index STATUS-index City-index Supplier- file De Smet S1-> 10 S1-> Athens S5-> S1 Janssens S2-> 20 S1->,S4-> London S1->,S4-> S2 Blanchart S3-> 30 S3->,S5-> Paris S2->,S3-> S3 Clark S4-> S4 Adams S5-> S5

  32. Sample file Supplier file SNUMBER SNAME STATUS CITY S1 De Smet 20 London S2 Janssens 10 Paris S3 Blanchart 30 Paris S4 Clark 20 London S5 Adams 30 Athens

  33. Organization with key transformation 0 1 S300 Blanchart 30 Paris 2 3 4 5 S200 Janssens 10 Paris 6 7 S500 Adams 30 Athens 8 9 S100 De Smet 20 London 10 11 S400 Clark 20 London 12

  34. Hierarchical Database Structure Department data element Project A data element Project B data element Employee 1 Employee 2 Text: O’Brien : p. 158

  35. Network Database Structure Department A Department B Employee 1 Employee 2 Employee 3 Project A Project B Text: O’Brien : p. 158

  36. Relational Structure Department D# DName location Manager D1 D2 D3 D4 D5 • Employee E# EName FUNCTION SALARY D# • E1 D1 • E2 D1 • E3 D2 • E4 D3 • E5 D3 Text: O’Brien : p. 158

  37. Object Oriented Structure • Attributes • client • status • interest • Operations • payment • withdrawal Object class Bank account Inheritance Object class Saving account Object class Checking account • Attributes • number of withdrawals • Quarterly statement • Operations • calculate interest • print quarterly statement • Attributes • credit limit • monthly statement • Operations • calculate interest • print status Text: O’Brien : p. 161

  38. Database Development 1. Data Planning 2. Specification gathering 3. Conceptual design 4. Physical design 5. Logical design Text: O’Brien : p. 164

  39. SUPERVISED by COMPANY CAR Data Modeling Entity-relation diagram EMPLOYEE Allocated MANAGER STAFFER Authorizes FREELANCER INVOICE submits Text: O’Brien : p. 165

More Related