database management chapter 4. data elements payroll fileremunerations employee database employee...
Post on 19-Dec-2015
218 views
TRANSCRIPT
![Page 1: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/1.jpg)
Database Management
Chapter 4
![Page 2: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/2.jpg)
![Page 3: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/3.jpg)
Data Elements
Payroll file Remunerations
Employee database
Employeerecord1
Name SSN Salary
Employeerecord2
Name SSN Salary
Employeerecord3
Name SSN Salary
Employeerecord4
Name SSN Salary
Text: O’Brien : p. 144
![Page 4: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/4.jpg)
Logical Data Elements
Fieldrepresents an attribute of an entity
RecordSet of attributes that describe an entity
Fileset of records of the same type
transaction file, archive file, ...
Database integrated set of logically related files and the
rules they have to satisfyText: O’Brien : p. 145
![Page 5: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/5.jpg)
Database
Employee database
Payroll data
Training data
Person data
![Page 6: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/6.jpg)
Database Management
User Inquiry
Applications
Database Management System
Corporate database
Request
Text: O’Brien : p. 146
![Page 7: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/7.jpg)
Example
CARS (model, #cylinders, origin, tax, fee)
Model #cyl origin tax fee Origin tax
------------------------------------------------------ #cyl fee
Rabit 4 Germany 15 30
Mustang 6 USA 0 45
Mirafiori 4 Italy 18 30
Accord 4 Japan 20 30
Cutlass 8 USA 0 60
Laguna 4 France 20 30
BMW 6 Germany 15 45
Velsatis 6 France 20 45
Velsatis 4 France 20 30
![Page 8: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/8.jpg)
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.
![Page 9: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/9.jpg)
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)
![Page 10: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/10.jpg)
Work with DBMS-software
Operating system
DBMS
Applicationprograms
Databases
DataDictionary
Database development Database inquiry Database maintenance Application development
Text: O’Brien : p. 147
![Page 11: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/11.jpg)
Dictionary Access
Text: O’Brien : p. 149
![Page 12: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/12.jpg)
Database Inquiry
Query LanguagesSQL
Select Name, department, salaryFrom Employee, PayrollWhere Employee.number = Payroll.number and Function = “Analyst”.
QBEEmployee Name number department num
Payroll number function salary num “ Analyst”
![Page 13: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/13.jpg)
DBMS
Userinterface
Querylanguage
Reportgenerator
DBMSutilities
Applicationgenerators
Data-dictionaryprograms
Host-languageProgramInterface
DBMSkernel program
![Page 14: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/14.jpg)
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
![Page 15: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/15.jpg)
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 )
![Page 16: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/16.jpg)
Types of Databases
Operational databases (subject area databases SADB) transaction databases, production databasesemployee database, stock database, ...
Analytical databases ( management databases )data retrieved from external and operational databasesAccessible for OLAP, DSS, management information systems
Data Warehousescontain historical data from various operational and external
databasescentral source for standardized and integrated data for
management and usersdata mart is a limited version for a specific subjectused for data mining
Text: O’Brien : p. 150
![Page 17: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/17.jpg)
Types of Databases (2)
Distributed databasesmostly for local needs, can be copiesconsistency problemsdistributed over servers related, eventually via WWW or intranet
Personal databases for end usersvarious data gathered by users on their workstationseventual with spreadsheet or DBMS
External databasesoften access chargestatistical , bibliographical
Text: O’Brien : p. 151-153
![Page 18: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/18.jpg)
Types of Databases (3)
Distributeddatabases
End-userdatabases
Data-warehouse
Managementdatabases
Opera-tional
databases
Externaldatabases
Networkserver
Client-PCor NC
Text: O’Brien : p. 151
![Page 19: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/19.jpg)
Hypermedia DB on the WEB
Client-PCor NC
WEB-browser
Networkserver
Hypermediadatabase
Internetintranetextranet
Web-serversoftware
parts of aWeb-information system
Text: O’Brien : p. 153
![Page 20: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/20.jpg)
Data Resource Management
Dataadministration
Data Planning
Database Administration
•Policy governing data ownership and access control•Data Resource Requirement Planning•Data model and data architecture
•Strategic and technical database planning•Define applications•Procedures for data retention•Operational procedures
•Physical database•Logical database•Data dictionary•HW and SW
Text: O’Brien : p. 154
![Page 21: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/21.jpg)
Benefits and Limitations
Benefits of a DBMS reduces data redundancy and integrates datadata accessible from all programs programs not dependent on data formatquery/answer and reporting facilitieseasier application development and programmingbetter integrity and security
Limitations of a DBMS increased complexity of the technologydevelopment of large databases is difficult and expensiveslower than file management systems due to additional layermore sensible for fraud, errors and failures
![Page 22: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/22.jpg)
Technical Principlesof database management
![Page 23: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/23.jpg)
Files
Physical Logical
AB
C
D
E
A
B
C
D
E
![Page 24: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/24.jpg)
Aspects of file usage
File activity
input 2000orders
processing
revenue state
inquiry
Clientfile
10000records
20%
100%
0.01%
AccessibilityEvolution
1 instruction
1 inquiry
![Page 25: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/25.jpg)
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.
![Page 26: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/26.jpg)
Database
Employee
Number Name City
STUDIES
Diploma year
CHILDREN
First name Birthday
12 Duval Rome
16 Krols Dublin
38 Smets Berlin
college 87
master math 94
econ 90
Accounting 94
college 91
Bache elec 94
john 120694inge 241198lidy 070296Carl 040894
type
occurrence
![Page 27: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/27.jpg)
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
![Page 28: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/28.jpg)
Supplier file with index on city
Supplier file
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
City-index
Athens .
London .
London .
Paris .
Paris .
![Page 29: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/29.jpg)
Index on city and index on status
Status-index
. 10
. 20
. 20
. 30
. 30
Supplier file
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
City-index
Athens .
London .
London .
Paris .
Paris .
![Page 30: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/30.jpg)
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
![Page 31: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/31.jpg)
File Organization: Indexed-sequential
multi-levelindex blocks
datablocks
BensDoomsFagin
AdamsAlbertBens
BodooClaesCoddDooms
ErnestFagin
AceAdams
AdemarAertsAlbert
BehrBens
Bodoo
parameters - index block size - data block size
![Page 32: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/32.jpg)
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
![Page 33: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/33.jpg)
Sample 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
Supplier file
![Page 34: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/34.jpg)
Organization with key transformation
S300 Blanchart 30 Paris
0 1
2 3
4 5
6 7
8 9
10 11
12
S200 Janssens 10 Paris
S500 Adams 30 Athens
S100 De Smet 20 London
S400 Clark 20 London
![Page 35: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/35.jpg)
Hierarchical Database Structure
Departmentdata element
Project Bdata element
Project Adata element
Employee 2Employee 1
Text: O’Brien : p. 158
![Page 36: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/36.jpg)
Network Database Structure
Department A Department B
Employee 1 Employee 2 Employee 3
ProjectA
ProjectB
Text: O’Brien : p. 158
![Page 37: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/37.jpg)
Relational Structure
Department D# DName location Manager
D1
D2 D3
D4
D5
Employee E# EName FUNCTION SALARY D#
E1 D1E2 D1E3 D2E4 D3E5 D3
Text: O’Brien : p. 158
![Page 38: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/38.jpg)
Object Oriented Structure
Attributes•client•status•interestOperations•payment•withdrawal
Attributes•number of withdrawals•Quarterly statement
Operations•calculate interest•print quarterly statement
Attributes•credit limit•monthly statement
Operations•calculate interest•print status
Object class Bank account
Object class Checking account
Object class Saving account
Inheritance
Text: O’Brien : p. 161
![Page 39: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/39.jpg)
Database Development
1.Data Planning
2. Specification gathering
3. Conceptual design
4. Physical design
5. Logical design
Text: O’Brien : p. 164
![Page 40: Database Management Chapter 4. Data Elements Payroll fileRemunerations Employee database Employee record1 Name SSN Salary Employee record2 Name SSN](https://reader037.vdocuments.us/reader037/viewer/2022110322/56649d3a5503460f94a14632/html5/thumbnails/40.jpg)
Data Modeling
Entity-relation diagram
EMPLOYEE
MANAGER
STAFFER
FREELANCER
COMPANY CAR
INVOICE
SUPERVISEDby
Allocated
Authorizes
submits
Text: O’Brien : p. 165