INTEGRITY CONSTRAINTS

Database System Concepts, Second Edition, Chapter 5, page 149

MK: QUIZ SISTEM BASIS DATADSN: MAM, RASP

UNIVERSITAS LAMPUNGFAKULTAS TEKNIKJURUSAN TEKNIK ELEKTROTAHUN AKADEMIK 2013 /2014

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 masing-masing 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 student-class 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 branch-name 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