1D0-541 Exam - CIW v5 Database Design Specialist

NEW QUESTION 1
Consider the following relation definition:
STUDENT(
Student_Number: integer NOT NULL
Name: variable length character string length 20 NOT NULL) Primary Key Student_Number
HOUSING(
Housing_ID: integer NOT NULL Student_Number: integer NOT NULL
Building: variable length character string length 25 NOT NULL) Primary Key Housing_ID
Foreign Key Student_Number References STUDENT(Student_Number)
ON DELETE NO CHECK ON UPDATE
Which integrity constraint is violated in this relation definition?

• A. Entity integrity
• B. Domain constraint
• C. Referential integrity
• D. Enterprise constraint

Answer: C

NEW QUESTION 2
Consider the Information Engineering diagram shown in the exhibit for a building management company. Referential integrity must be maintained such that a building cannot be deleted when it has residents. Building_ID, R_ID, Room_Count and Room_Num are integer numbers, whereas Bldg_Name, Location and Res_Name are all represented by variable-length strings with a maximum of 20 characters. Which SQL statement best implements the relations shown in this diagram?

• A. CREATE TABLE BUILDING (Building_ID INTEGER NOT NULL PRIMARY KEY,Bldg_Name VARCHAR (20),Location VARCHAR (20), Room_Count INTEGER ); CREATE TABLE RESIDENT ( R_ID NOT NULL PRIMARY KEY,Room_Num INTEGER, Res_Name VARCHAR (20), Building_ID INTEGER NOT NULL,FOREIGN KEY Building_ID REFERENCES RESIDENT (Building_ID) ON DELETE NO CHECK);
• B. CREATE TABLE BUILDING (Building_ID INTEGER NOT NULL PRIMARY KEY,Bldg_Name VARCHAR (20),Location VARCHAR (20), Room_Count INTEGER ); CREATE TABLE RESIDENT ( R_ID NOT NULL PRIMARY KEY,Room_Num INTEGER, Res_Name VARCHAR (20), Building_ID INTEGER NOT NULL,FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID) ON DELETE NO CHECKON UPDATE CASCADE);
• C. CREATE TABLE BUILDING (Building_ID INTEGER NOT NULL PRIMARY KEY,Bldg_Name VARCHAR (20),Location VARCHAR (20), Room_Count INTEGER ); CREATE TABLE RESIDENT ( R_ID NOT NULL PRIMARY KEY,Room_Num INTEGER, Res_Name VARCHAR (20), Building_ID INTEGER NOT NULL,FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID) ON DELETE NO CHECKON UPDATE CASCADE);
• D. CREATE TABLE BUILDING (Building_ID INTEGER NOT NULL PRIMARY KEY,Bldg_Name VARCHAR (20),Location VARCHAR (20), Room_Count INTEGER ); CREATE TABLE RESIDENT ( R_ID NOT NULL PRIMARY KEY,Room_Num INTEGER, Res_Name VARCHAR (20), Building_ID INTEGER NOT NULL,FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID) ON DELETE NO CHECKON UPDATE CASCADE);

Answer: C

NEW QUESTION 3
Consider the Stu_Act and Act_Fee tables shown in the exhibit. Which relational algebraic operation would yield the Activity Relation table in the exhibit?

• A. Union
• B. Intersection
• C. Natural join
• D. Cartesian product

Answer: C

NEW QUESTION 4
Consider the relational database shown in the exhibit. What is the foreign key in this database?

• A. Employee.Dept_ID
• B. Dept_Mngr
• C. Dept_Name
• D. Department.Dept_ID

Answer: A

NEW QUESTION 5
What is the highest normal form of the relation(s) shown in the exhibit?

• A. Mastered
• B. Not Mastered

Answer: A

NEW QUESTION 6
Consider the entity-relationship (ER) diagram shown in the exhibit. What do the characters at the ends of the connecting line indicate?

• A. Degree of a relation
• B. Cardinality of a relation
• C. Primary key of a relation
• D. Determinant of a relation

Answer: B

NEW QUESTION 7
Consider the Orders relation shown in the exhibit. Which of the following SQL statements would return all complete tuples for order dates in 2002, arranged by amount from lowest to highest?

• A. SELECT *FROM OrdersWHERE Order_Date LIKE _02 ORDER BY Amount;
• B. SELECT (Order_Date, Amount) FROM OrdersWHERE Order_Date LIKE %02 ORDER BY Amount;
• C. SELECT *FROM OrdersWHERE Order_Date LIKE _02 ORDER BY Order_No;
• D. SELECT *FROM OrdersWHERE Order_Date LIKE %02 ORDER BY Amount;

Answer: D

NEW QUESTION 8
Which security technique limits access by unauthorized users to parts of an enterprise database?

• A. Views
• B. Concurrency
• C. Locking
• D. Integrity controls

Answer: A

NEW QUESTION 9
Consider the following SQL statement and the Orders relation shown in the exhibit:

What is the output of this SQL statement?

• A. [MISSING]
• B. [MISSING]
• C. [MISSING]
• D. [MISSING]

Answer: C

NEW QUESTION 10
Which area of database security involves maintaining access to enterprise data?

• A. Integrity
• B. Privacy
• C. Availability
• D. Confidentiality

Answer: C

NEW QUESTION 11
Which relational algebraic operation is used to select specific columns (attributes) from a relation?

• A. Mastered
• B. Not Mastered

Answer: A

NEW QUESTION 12
Which subset of Structured Query Language (SQL) is used to perform operations such as data retrieval or deletion on the data contained in a database?

• A. Data Control Language
• B. Data Definition Language
• C. Data Formatting Language
• D. Data Manipulation Language

Answer: D

Explanation:

NEW QUESTION 13
Which subset of Structured Query Language (SQL) is used to limit access to a database or its data?

• A. Data Definition Language
• B. Data Control Language
• C. Data Entity Language
• D. Data Manipulation Language

Answer: B

NEW QUESTION 14
Consider the table shown in the exhibit. Which relational algebraic operation would return Row 3?

• A. Union
• B. Selection
• C. Projection
• D. Difference

Answer: B

NEW QUESTION 15
Consider the relation shown in the exhibit. Which of the following SQL statements would return a relation that excludes all customers with a Satisfaction_Rate of less than or equal to 80 unless the Sales_Office is located in Atlanta?

• A. SELECT *FROM CustomersWHERE Satisfaction_Rate > 80 OR Sales_Office = Atlanta;
• B. SELECT *FROM CustomersWHERE Satisfaction_Rate <= 80 AND Sales_Office = Atlanta;
• C. SELECT *FROM CustomersWHERE Satisfaction_Rate >= 80;
• D. SELECT *FROM CustomersWHERE Satisfaction_Rate >= 80 AND NOT Sales_Office = Atlanta;

Answer: A

NEW QUESTION 16
Your enterprise is involved in planning a database project. The exhibit shows the result of
one phase of the database design life cycle. Which term best describes the diagram shown in the exhibit?

• A. Information Engineering (IE) data model
• B. Corporate data model
• C. Database requirements model
• D. Entity Relation Data (ERD) model

Answer: B

NEW QUESTION 17
Consider the Registration relation shown in the exhibit. Which of the following SQL statements would return the Registration2 relation from the Registration relation?

• A. SELECT Course_Code FROM Registration;
• B. SELECT *FROM RegistrationWHERE Registration_ID = 1003 AND Registration_ID = 1005;
• C. SELECT *FROM RegistrationWHERE Course_Code = 'A4343';
• D. SELECT Registration_ID, Student_ID, First_Name, Last_NameFROM RegistrationWHERE Course_Code = 'A4343';

Answer: C

NEW QUESTION 18
Which type of relational integrity is violated if a primary key in a database has a null value?

• A. Entity integrity
• B. Domain integrity
• C. Domain constraints
• D. Referential integrity

Answer: A

NEW QUESTION 19
......