CIW DATABASE SPECIALIST

1.A foreign key maps to a:
A.prime key.
B.indirect key.
C.parent key.
D.composite key.
Correct:C
2.Consider the relation shown in the exhibit. Which of the following SQL statements would properly add information for a new employee?

A.INSERT INTO Employee VALUES(0005, Tim, Bogart, 03-15-77);
B.INSERT INTO Employee(Emp_ID, First_Name, Last_Name, Birth_Date) VALUES(0004, Tim, Bogart, 03-15-77);
C.INSERT INTO Employee(Emp_ID, First_Name, Last_Name, Birth_Date) VALUES(0005, Tim, Bogart, 03-05-77);
D.INSERT INTO Employee (Emp_ID, First_Name, Last_Name, Birth_Date) VALUES (0005, Tim, Bogart, 03-05-77);
Correct:D
3.Which pair of relational algebraic operations requires union compatibility?
A.Union and join
B.Selection and projection
C.Intersection and difference
D.Cartesian product and intersection
Correct:C
4.Which static member of the ResultSet class should be used to create an updatable result set?
A.ResultSet.TYPE_FORWARD_ONLY
B.ResultSet.TYPE_FORWARD_UPDATABLE
C.ResultSet.TYPE_SCROLL_INSENSITIVE
D.ResultSet.TYPE_SCROLL_SENSITIVE
Correct:D
5.Which method of the Statement interface could be used to delete data from a database?
A.executeUpdate
B.executeQuery
C.close
D.clearBatch
Correct:A
6.Which JDBC interface is used to extract information about the database schema?
A.ResultSet
B.Connection
C.DatabaseMetaData
D.ResultSetMetaData
Correct:C
7.Which of the following statements is true of the Connection interface?
A.Each JDBC client application must provide a class that implements the Connection interface.
B.Each JDBC driver provides a class that implements the Connection interface.
C.Each JVM provides a class that implements the Connection interface.
D.The Connection interface can be used to load a JDBC driver.
Correct:B
8.Consider the Entity-Relation diagram shown in the exhibit. When the logical database design phase is completed, which of the following is a valid DBDL description of the base relations for the Entity-Relation diagram?

A.STUDENT( Student_Number: integer NOT NULL Name: variable length character string length 20 NOT NULL) Primary Key Student_Number CLASS( Class_Num: integer NOT NULL Class_Name: integer NOT NULL) Primary Key Class_Num
B.STUDENT( Student_Number: integer NOT NULL Name: variable length character string length 20 NOT NULL) Primary Key Student_Number CLASS( Class_Num: integer NOT NULL Class_Name: integer NOT NULL) Primary Key Class_Num Foreign Key Class_Num References STUDENT
C.STUDENT( Student_Number: integer NOT NULL Name: variable length character string length 20 NOT NULL) Primary Key Student_Number STU_CLASS( Student_Number: integer NOT NULL Class_Num: integer NOT NULL) Primary Key Student_Number CLASS( Class_Num: integer NOT NULL Class_Name: integer NOT NULL) Primary Key Class_Num
D.STUDENT( Student_Number: integer NOT NULL Name: variable length character string length 20 NOT NULL) Primary Key Student_Number STU_CLASS( Student_Number: integer NOT NULL Class_Num: integer NOT NULL) Primary Key Student_Number, Class_Num CLASS( Class_Num: integer NOT NULL Class_Name: integer NOT NULL) Primary Key Class_Num
Correct:D
9.What is the highest normal form of the relation(s) shown in the exhibit?

A.Boyce-Codd normal form
B.First normal form
C.Second normal form
D.Third normal form
Correct:C
10.Consider the following domain description: domain Student_ID: integer domain Grade: fixed length character string length 1 To meet business needs, you must add enterprise constraints to this domain description. The Student_ID should always be a positive integer. The initial value of Student_ID should be 0 (zero) to indicate that a valid ID number has not been assigned. The Grade should be limited to the letters A through F. Which SQL statements would perform these tasks?
A.CREATE DOMAIN Student_ID AS INTEGER DEFAULT 0 CHECK ( Student_ID > -1); CREATE DOMAIN Grade AS CHAR(1); CHECK (Student_ID IN ('A','B','C','D','E','F'));
B.CREATE DOMAIN Student_ID AS INTEGER CHECK (Student_ID > -1); CREATE DOMAIN Grade AS CHAR(1); DEFAULT NULL CHECK (Student_ID IN ('A','B','C','D','E','F'));
C.CREATE DOMAIN Student_ID AS INTEGER; CREATE DOMAIN Grade AS CHAR(1); CONSTRAINT ENTERPRISE CHECK;
D.CREATE TABLE ENTERPRISE ( Student_ID INTEGER NULL Grade VARCHAR(1) NOT NULL, CONSTRAINT ENTERPRISE CHECK;
Correct:A
11.Assuming that conn references a valid and open connection to the database, which code segment will insert values into the Employees relation?
A.conn.executeUpdate (INSERT INTO Employees VALUES + (1001, 'Karen Hughes', 55000));
B.Statement s = conn.createStatement(); s.execute(INSERT INTO Employees VALUES + (1001, 'Karen Hughes', 55000));
C.Statement s = conn.createStatement(); s.executeQuery(INSERT INTO Employees VALUES + (1001, 'Karen Hughes', 55000));
D.Statement s = new Statement(); s.executeUpdate(INSERT INTO Employees VALUES + (1001, 'Karen Hughes', 55000));
Correct:B
12.Which term describes one or more database operations that are executed as a single unit?
A.Update
B.Transaction
C.Encapsulation
D.Operational group
Correct:B
13.What is the highest normal form of the relation(s) shown in the exhibit?

A.Second normal form
B.Third normal form
C.No normal form
D.First normal form
Correct:C
14.Consider the following SQL statement: SELECT * FROM Orders WHERE Order_Date LIKE %02 ORDER BY Sales_Rep_No, Amount DESC; Using the Orders Relation shown in the exhibit, which of the following tables shows the result of this SQL statement?

A.Exhibit Option A
B.Exhibit Option B
C.Exhibit Option C
D.Exhibit Option D
Correct:B
15.Consider the following stored procedure: CREATE PROCEDURE showFees AS SELECT Fee FROM ACTIVITY WHERE Fee > 0 Which Java code segment will correctly utilize this stored procedure?
A.CallableStatement cs = conn.prepareCall({call showFees}); ResultSet rs = cs.executeQuery();
B.CallableStatement cs = conn.prepareCall({call showFees}); ResultSet rs = cs.execute();
C.PreparedStatement ps = conn.prepareStatement(SELECT Fee + FROM ACTIVITY + WHERE Fee > 0); ResultSet rs = cs.execute();
D.PreparedStatement ps = conn.prepareStatement(SELECT Fee + FROM ACTIVITY + WHERE Fee > 0); ResultSet rs = cs.execute();
Correct:A
16.Which Statement interface methods are used to execute a SQL select query?
A.executeUpdate and close
B.executeUpdate and execute
C.executeQuery and execute
D.executeUpdate and executeQuery
Correct:C
17.What is the purpose of the batch update feature in JDBC 2.0?
A.To reduce processing time
B.To enable transaction processing
C.To provide enhanced security
D.To generate result sets
Correct:A
18.With regard to databases, what is normalization?
A.The process of reducing the cardinality of a relation
B.The process of organizing and refining relations
C.The process of duplicating data to reduce the number of tables
D.The process of limiting data stored in a table to a specific range of values
Correct:B
19.Consider the Information Engineering diagram in the exhibit showing the relations BUILDING and RESIDENT. What is the relationship between BUILDING and RESIDENT?

A.1:1
B.1:N
C.N:1
D.M:N
Correct:B
20.Consider the relations shown in the exhibit. Due to restructuring, the Sales department has been eliminated and the employees working in that department have been dismissed. All ID information is stored as integers. Which SQL statement would be used to return a relation with all information for the employees who have been dismissed?

A.SELECT * FROM Employee;
B.SELECT ID, Last_Name FROM Employee; WHERE ID = 0004;
C.SELECT * FROM Employee; WHERE Dept_ID = 022;
D.SELECT * FROM Employee WHERE Dept_ID = 022;
Correct:C
21.Consider the Information Engineering diagram shown in the exhibit. Building_ID, R_ID, Room_Count and Room_Num are integer numbers, whereas Bldg_Name and Res_Name are represented by variable-length strings with a maximum of 20 characters. Location can be up to 50 characters long, and no building has more than 600 rooms. Which SQL statement best implements the BUILDING relation shown in this diagram?

A.CREATE TABLE BUILDING ( Building_ID NOT NULL PRIMARY KEY, Bldg_Name, Location, Room_Count);
B.CREATE TABLE BUILDING ( Building_ID NOT NULL PRIMARY KEY, Bldg_Name, Location, Room_Count, FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID));
C.CREATE DOMAIN ID AS INTEGER; CREATE DOMAIN COUNT AS INTEGER CHECK (COUNT <= 600 And COUNT > -1); CREATE DOMAIN NAME AS VARCHAR (50); CREATE TABLE BUILDING ( Building_ID ID NOT NULL PRIMARY KEY, Bldg_Name NAME, Location NAME, Room_Count COUNT, FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID));
D.CREATE TABLE BUILDING ( Building_ID INTEGER NOT NULL PRIMARY KEY, Bldg_Name VARCHAR (20), Location VARCHAR (50), Room_Count INTEGER CHECK ( Room_Count > -1 And Room_Count < 601));
Correct:D
22.Consider a driver class named COM.greatDBco.JDBCDriver. Which of the following is the best method for loading the driver?
A.Explicitly instantiate the driver from within a program as follows: new COM.greatDBco.JDBCDriver();
B.Instantiate the driver from the command line using the D option as follows: java Djdbc.drivers=COM.greatDBco.JDBCDriver MyJDBCProgram
C.Dynamically load the driver using the Class class as follows: Class.forName(COM.greatDBco.JDBCDriver);
D.Dynamically load the driver using the DriverManager class as follows: DriverManager.load(COM.greatDBco.JDBCDriver);
Correct:C
23.Which relational algebraic operation is used to select specific columns (attributes) from a relation?
A.Union
B.Difference
C.Projection
D.Intersection
Correct:C
24.Which statement best describes a candidate key?
A.It is the primary key for an entity.
B.It uniquely identifies every instance of an entity.
C.One or more keys are joined together to form a composite key.
D.One or more keys may be used to form a primary key.
Correct:D
25.Which of the following code fragments performs the actions required to connect to the database at URL jdbc:myDriver:myDatabase using the MyDriver driver?
A.Class.forName(MyDriver); DriverManager.getConnection (jdbc:myDriver:myDatabase);
B.DriverManager.getConnection (jdbc:myDriver:myDatabase); Class.forName(MyDriver);
C.DriverManager.getConnection(MyDriver); Class.forName (jdbc:myDriver:myDatabase);
D.Class.forName(jdbc:myDriver:myDatabase); DriverManager.getConnection (MyDriver);
Correct:A
26.Consider the following code fragment: 1. Statement s = conn.createStatement(); 2. 3. s.executeUpdate(CREATE TABLE MyTable ( + 4. ColumnA CHAR(5), + 5. ColumnB CHAR(5))); 6. 7. s.executeUpdate(INSERT INTO MyTable VALUES ( + 8. '00001', 'AAAAA'); 9. s.executeUpdate(INSERT INTO MyTable VALUES ( + 10. '00002', 'AAAAA'); 11. 12. ResultSet rs = s.executeQuery(SELECT * FROM MyTable + 13. WHERE ColumnB = 'AAAAA'); 14. while(rs.next()) 15. System.out.println(rs.getString(2)); Assume that variable conn references a valid and open Connection object, and that the database does not already contain a table named MyTable. Which output is generated?
A.00001 AAAAA
B.AAAAA
C.1 2
D.A SQLException is thrown at line 15 and no output is generated.
Correct:B
27.Which of the following best describes the information contained in the data dictionary (or system catalog)?
A.Metadata
B.File access tables
C.A relational database
D.Sequential data objects
Correct:A
28.A government agency provides driving licenses for various types of motor vehicles. A requirement for a particular license may be possession of another type of motor vehicle license. The agency is developing a relational database system. The exhibit shows an Information Engineering diagram for the relation LICENSES. After the resolution of any anomalies, what would be the correct DBDL description for the logical data model?

A.LICENSES(lic_id,lic_name, cost, qualifying_lic) Primary Key lic_id QUALIFICATIONS(qualification_num, qualifying_lic) Primary Key qualification_num Foreign Key qualification_num references Licenses(lic_id)
B.LICENSES(lic_id,lic_name, cost, qualifying_lic) Primary Key lic_id Foreign Key qualifying_lic
C.LICENSES(lic_id,lic_name, cost, qualifying_lic) Primary Key lic_id QUALIFICATIONS(qualification_num,lic_id,qualifying_lic) Primary Key qualification_num
D.LICENSES(lic_id,lic_name, cost, qualifying_lic) Primary Key lic_id QUALIFICATIONS(qualification_num, lic_id, qualifying_lic) Primary Key qualification_num Foreign Key qualifying_lic references Licenses(qualifying_lic)
Correct:D
29.What of the following is a characteristic of the three-tier database architecture?
A.A Web browser is used as the application server.
B.The application logic is centralized on a dedicated server.
C.A thick client is used to perform business application logic functions locally.
D.Database application logic and database functionality are integrated and reside on a common server.
Correct:B
30.Consider the following relation definitions: domain s_id: integer domain grd: fixed length character string length 1 STUDENT_GRADE( Student_Number: s_id Grade: grd ) Primary Key Student_Number Which integrity constraint is violated in this relation definition?
A.Entity integrity
B.Domain constraint
C.Referential integrity
D.Enterprise constraint
Correct:A