SBD3_Integrity_Constraint

INTEGRITY CONSTRAINTS
Database System Concepts, Second
Edition, Chapter 5, page 149
UNIVERSITAS LAMPUNG
FAKULTAS TEKNIK
JURUSAN TEKNIK ELEKTRO
TAHUN AKADEMIK 2013 /2014
MK: QUIZ SISTEM BASIS DATA
DSN: MAM, RASP
2013 Quiz Sistem Basis Data [1]
• NBM ganjil Soal 1: Tunjukkan tipe (data base)
user yg akan melakukan fungsi-fungsi berikut
untuk sistem gaji di dalam sebuah universitas
besar; (a) Menulis sebuah program aplikasi
untuk menghasilkan dan mencetak resi; (b)
Mengubah alamat di dalam data base untuk
seorang karyawan yang pindah; (c) Membuat
user account baru untuk petugas klerikal yang
baru direkrut.
2013 Quiz Sistem Basis Data [2]
• NBM genal soal 1: Pikirkanlah data base milik
sebuah perusahaan televisi berbayar yang
memuat nama pelanggan, alamat, kategori
layanan (televisi kabel, televisi satelit, televisi
protokol internet), dan informasi penagihan.
Tentukanlah permission level untuk masingmasing data base user (petugas penagihan,
petugas perbaikan, dan petugas customer
service).
2013 Quiz Sistem Basis Data [3]
• NBM ganjil soal 2: What are the main
differences between a file-processing system
and a data base management system?
• NBM genap soal 2: Explain the difference
between physical and logical data
independence?
2013 Quiz Sistem Basis Data [4]
• NBM ganjil soal 3:
2013 Quiz Sistem Basis Data [5]
• NBM genap soal 3: Bagai mana derajat dan
kardinalitas dari relasi SUPPLIER berikut ini?
2013 Quiz Sistem Basis Data [6]
• NBM ganjil soal 4: Construct an E-R diagram
for a university registrar’s office. The office
maintains data about each class, including the
instructor, the enrollment, and the time and
place of the class meetings. For each studentclass pair, a grade is recorded.
2013 Quiz Sistem Basis Data [7]
• NBM genap soal 4: Construct an E-R diagram
for a car insurance company with a set of
customers, each of whom owns a number of
cars. Each car has a number of recorded
accidents associated with it.
Materi
•
•
•
•
•
Domain Constraints.
Referential Integrity.
Functional dependencies.
Assertions.
Triggers.
INTEGRITY CONSTRAINTS
• ... Provide a means of ensuring that changes
made to the database by authorized users do
not result in a loss of data consistency. Thus,
integrity constraints guard against accidental
damage to the database.
• We have already seen a form of integrity
constraint for the E-R model. These
constraints were in the form of ...
• Key declaration – the stipulation that certain
attributes form a candidate ke for a given entity
set. The set of legal insertions and updates are
constrained to those that do not create two
entities with the same value on a candidate key.
• Form of a relationship – many to many, one to
many, one to one, or one to many relationship
restrics to set of legal relationship among entities
of a collection of entity sets.
In general,
• An integrity constraint can be an arbitrary
predicate pertaining to the database.
However, arbitrary predicates may be costly to
test. Thus we usually limit ourselves to
integrity constraints that can be tested with
minimal overhead.
• DOMAIN CONSTRAINT – A domain of possible
values must be associated with every
attributes.
How such
• Constraints are specified in the SQL
(Structured Query Language) DDL (Data
Definition Language).
• Domain constraints are the most elementary
form of integrity constraint. They are tested
easily by the system whenever a new data
item is entered into the database.
Domain types
• It is possible for several attributes to have the
same domain.
• For example, the attibutes customer-name
and employee-name might have the same
domain, the set of all person names.
• However, the domains of balance and branchname certaintly ought to be distinct.
• It is perhaps less clear whether ...
... Customer-name and ...
• Branch-name should have the same domain. At
the implementation level, both customer-names
and branch names are character strings.
• However, we would normally not consider the
query “Find all customers who have the same
name as a branch” to be a meaningful query.
• Thus, if we view the database at the conceptual
rather than physical level, customer-name and
branch-name should have distinct domains.
We can see that
• A proper definition of domain constraints not
only allows us to test values inserted in the
database but also permits us to test queries to
ensure that the comparisons made make
sense.
• The principle behind attribute domains is
similar to that behind typing of variable in
programming languages.
Strongly typed programming
languages
• Allow the compiler to check the program in
greater detail.
• However, strongly typed language inhibit
“clever hacks” that are often required for
system programming.
• Since database systems are designed to
support users who are not computer experts,
the benefits of strong typing often outweigh
the disadvantages.
Nevertheless,
• Many existing system allow only a small
number of types domains. Newer systems,
particularly object-oriented database system,
offer a rich set of domain types that can be
extended easily.
• Domain types in SQL