www.cardiff.ac.uk/medic/irg-clinicalepidemiology
Database systems
Copyright By PowCoder代写 加微信 powcoder
Information modelling
& database systems
What is a database system?
What is database study about?
What do we study in this module?
Data management
Data management
the hierarchy of data:
bit (binary digit): a circuit that is either on or off
byte: 8 bits
character: each byte represents a character; the basic building block of information
field: number or characters that describe an aspect of an object or activity
record: a collection of related data fields
file: a collection of related records
database: a collection of integrated & related files
The hierarchy of data
Data management approaches
records can be stored in a file or in a database
traditional approach: separate data files are created for each application
results in data redundancy (duplication)
data redundancy conflicts with data integrity
(i.e. accuracy & consistency of stored data)
database approach: pool of related data is shared by multiple users & applications
significant advantages over traditional approach
Traditional data management approach
Database approach
Database approach
Data and databases
a database is a collection of related data, information and sometimes even knowledge
bank transactions
holiday bookings
library systems
customer bases
supermarket transactions
Database system
a computerised record keeping system
maintain data and make them available on demand
simple concept in theory, but not in practice
important questions:
How do we represent real–world entities?
How do we handle billions of records?
How do we maintain data integrity?
Components of a database system
database management system
end users,
application programmers,
database administrators
software that facilitates the usage and management of the database
a collection of persistent data
Database approach – advantages
improved strategic use of corporate data
reduced data redundancy
improved data integrity
easier modification & updating
data & program independence
better access to data & information
standardization of data access
a framework for program development
better protection of data
shared data and information resources
Database approach – disadvantages
ANSI/SPARC architecture
ANSI – American National Standards
SPARC – Standards Planning and Requirements Committee
in the early 1970s, ANSI and SPARC defined a framework for data modelling based on degrees of data abstraction
ANSI/SPARC architecture defines three levels of data abstraction:
external – how data is viewed by individual end users
conceptual – how data is viewed logically in its entirety
internal – how the data is physically stored
ANSI/SPARC (three schema) architecture
internal view
conceptual view
external view
external view
external view
conceptual
Database management system
Database management system (DBMS)
DBMS: a set of computer programs that controls the creation, maintenance & use of a database
data definition and manipulation
data security, integrity and recovery
concurrency and high performance
provides an interface between:
database & application programs
database & users
often used with other software packages or the Internet, where a DBMS can act as:
front–end application: interacts with users
back–end application: interacts with applications
Database management system (DBMS)
e.g. PostgreSQL, MySQL, MS Access, SQL Server, Oracle, DB2, etc.
when choosing a DBMS consider:
size of the database
number of concurrent users
performance
interoperability with other systems
vendor considerations
Oracle DBMS
ORACLE was founded by and co. in 1977, and was the first to reach the market
the current version is Oracle11g, and Oracle Express is free to download
ORACLE is a leading DBMS vendor, offering a wide range of languages/tools for developing database applications, e.g.
SQLPlus and PL/SQL
a range of host languages (the Pro* family)
data Warehousing
Web-DB and networking
a software library that
implements an SQL
database engine
self–contained
serverless
zero–configuration
transactional
most widely deployed
Generic DBMS architecture
transaction
user requests
locating records on the disk quickly
generating
an optimised execution plan
responsible for the integrity of the database system
Features of DBMS
data independence
data integrity
concurrency
data dictionary
Data independence
knowledge of physical data organisation and access methods is not part of applications logic and code
it refers to the immunity of user applications to changes made in the definition and organisation of data
Data integrity
ensuring that the data remains accurate & consistent
data integrity data corruption
data integrity data security
inconsistency between two entries in a database which purport to represent the same fact is an example of lack of integrity
e.g. two different dates of birth for the same person
one way of ensuring consistency is by making sure that the fact is represented only once in the database, i.e. there is no redundant data in the database
protecting data against unauthorised disclosure, alteration or destruction
different access privileges allocated to users at various levels
complete database
individual tables
individual rows and columns of a table
according to records with particular values
an “owner” of a database has complete access and grants access privileges to other users
access checks can be established for each type of access, e.g. retrieve, modify, delete, etc.
Concurrency
ability to allow multiple users to perform multiple transactions at the same time
one of the main properties that separates a database from other forms of data management like spreadsheets
one user is changing data but has not yet saved (committed) those changes
then other users who query the same data should not be allowed to view the changed, unsaved data
instead other users should only view the original data
Data dictionary
a centralised repository of information about data such as meaning, relationships to other data, origin, usage and format
names and descriptions all tables, records and fields
data dictionaries do not contain any actual data from the database, only bookkeeping information for managing it
most DBMSs keep the data dictionary hidden from users to prevent them from accidentally destroying its contents
software developers refer to data dictionary during the development and maintenance of a database
Database study
What is database study about?
database design – How to gather requirements and design a useful database?
database programming – How to write code to implement and manipulate a database?
DBMS development – How to build a DBMS?
in this module, we focus on questions 1–2 at an introductory level
Database design
requirements:
Design a database which will record information about students and modules.
Each student is described by his or her ID, name and address.
Each module is described by its ID and title.
The database also records information about which student is taking which module.
implementation as tables
SID name address
S123 S Davies 1 High St
… … …
S345 P Evans 5 Main Rd
Database programming
application
database server
web server tier
application tier
database tier
we focus on this tier in this module
Database programming (SQL)
data definition
CREATE TABLE Student
SID CHAR(4),
name VARCHAR(30),
address VARCHAR(50),
data manipulation
SELECT SID
FROM Student
WHERE name = ‘P Evans’;
we will study both in this module
Database programming (embedded)
1) EXEC SQL BEGIN DECLARE SECTION;
2) int flight; /* flight number */
3) char date[10]; /* flight date in SQL format */
4) char seat[3]; /* two digit and a letter representing a seat */
5) int occ; /* a boolean to tell if seat is occupied */
6) EXEC SQL END DECLARE SECTION
7) void chooseSeat() {
8) /* C code for user data input */
9) EXEC SQL SELECT occupied INTO :occ
10) FROM Flights
11) WHERE fltNum = :flight AND fltDate = :date AND fltSeat = :seat;
12) if (!occ) {
13) EXEC SQL UPDATE Flights
14) SET occupied = ‘B1’
15) WHERE fltNum = :flight AND fltDate = :date AND fltSeat = :seat;
16) /* some C and SQL code to inform the user of the assignment */
18) else /* C code to notify user of unavailability and ask for another seat selection */
Recommended reading
CJ Date “Introduction to Database Systems,” 6th or 7th edition, Addison- Silberschatz, HF Korth, S Sudarchan “Database System Concepts,” 4th edition, McGraw-Hill
R Elmasri and SB Navathe “Database Systems,” 6th edition, Garcia–Molina, JD Ullman, J Widom “Database Systems – The complete book,” 2nd edition,
T Connolly, C Begg, A Strachan “Database Systems: A Practical Approach to Design, Implementation and Management,” 2nd edition, Addison- Ramakrishnan, J Gehrke “Database Management Systems,” 3rd edition, McGraw-Hill
R Earp, S Bagui “Learning SQL, a step-by-step guide using Oracle,”
/docProps/thumbnail.jpeg
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com