Question

D. Check that integrity constraints are addressed

Document that all integrity constraints are considered in the design. Create checklists to ensure that all four levels of integrity constraint – domain, entity, referential, and user – have been addressed. For example,

• Entity integrity requires that each table contains a primary key that uniquely identifies each row in the table, preventing duplicate rows.
• Domain integrity requires that the data entered is appropriate. A domain is defined as the set of all unique values permitted for an attribute. For example, a domain of date is the set of all possible valid dates, a domain of integer is all possible whole numbers, a domain of day-of-week is Monday, Tuesday ... Sunday.
• Referential integrity focuses on maintaining synchronization between the data in related tables. It is generally enforced through the use of primary key-foreign key combinations.
• User integrity constraints are based upon business model specifics (i.e. an integrity constraint rule that prevents a member from renting more than five DVDs at any one time) and are entirely specific to the specific database.

Answer 1

Hi there,

please find the query addressing all the constrains.

CREATE DOMAIN film_score AS DECIMAL(5,2)

(

film_id number(20) PRIMARY KEY,

film_name char(20) FOREIGN KEY,

genre char(10),

rating number(5) CONSTRAINT rating CHECK (value >=0) NOT DEFERRABLE,

views_count number(10)

country char(10)

casting char (20) NOT NULL

);

CREATE Film_details

(total_collection_amount number(20),

film_name char(20) PRIMARY KEY,

releasing_date(10),

making_cost(10),

);

Feel free to write back in case of doubt.

Thanks

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10)
);