Software Enginnering - Test 2

Know that a Statement object is created from a Connection object.

-Used for executing insert statements and queries -Statement object used for all SQL -Any SQL command typed int at the SQL prompt can be executed by the statement object

Know how retrieve the column values from a ResultSet

-While(rs.next()) //Allow access to the next row of data, by default rs is always initialized just before 1st row -{rs.getString(1)} //data from the 1st column of this row

Understand the 2 types of SQL statements that JDBC can invoke (Query/DML)

-execute: insert/update/delete (DML) -executeQuery: boolean -no semicolon

Know how multiplicity affects the implementation of data fields.

-one to many : ArrayList -many to many: association class -one to one: model view controller but no question probably

-hollow diamond: aggregation (do not destroy to containee if container is destroyed; e.g. destroy car not parts)

-solid: composition (destroy containee, destroy building then also the rooms inside)

Know what a Gantt chart is and what it consists of

-tasks to be completed, start/end time of task -black bars are top level tasks -white bars are subtasks -diamonds are milestones; deadlines

Build all constraints for all tables in this order:

1. Build all PK contraints (each table) 2. Build all FK constraints (one-many, or on the relationship built in many-many) 3. Build all not NULL

Data is inserted into parents tables then child table However, data is deleted from child tables first then parent tables

DML can violate PK, FK, and not null constraints: -insert violate primary key: insert multiple times -delete violate foreign key: try to delete parent first -not null: update primary key to null -wont let you

Delete all data syntax:

Delete from table_name;

How do you recognize how many tables are to be created based on the ER diagram?

Each rectangle is an independent table Also, many-many relationships must have linkage table

How can you detect primary key?

In a circle, underlined

Understand the access qualifiers symbols for data fields and methods: private protected public package

Private - protected # public + package ~

Understand what a ResultSet is and how it is returned.

ResultSet rs = stmt.executeQuery(" "); -collection type of object -iterate over collection

[Fig 3] Consider the following statement: Delete from owns; Assume that all Primary key/Foreign key constraints have been created on all tables as illustrated on the E-R diagram. Which of the following is true of the above statement: a. Correctly deletes all records of owns table (no error message displayed) b. Violates foreign key constraints implemented on owns table (error message is displayed) and does not delete any records c. Violates rule that you can never delete all records from a table (error message is displayed ) and does not delete any records d. None of the above

a. Correctly deletes all records of owns table (no error message displayed)

[Fig 3] Consider the following statement: Update owns Set owner_id = NULL; Which of the following statements are true? a. The Primary key constraint is violated because Primary keys cannot be NULL b. Is illegal because no column in a database can ever by NULL c. Is illegal because update statements always require a "where" clause d. All of the above

a. The Primary key constraint is violated because Primary keys cannot be NULL

What type of relationship is shown in Fig. 1? a. Unary b. Binary c. Ternary d. Cannot tell from the diagram.

a. Unary

A result set is an object that is somewhat similar to a collection and... a. contains the results of an SQL Select statement. b. allows the program to execute an SQL statement. c. predicts the outcome of an SQL statement. d. provides rules for how an SQL statement should be executed.

a. contains the results of an SQL Select statement.

Which Statement interface method should be used to execute an INSERT statement? a. execute b. executeStatement c. executeQuery d. executeSQL

a. execute

What is the primary key of the Person table? a. id b. person c. name d. related

a. id

PK constraint syntax:

alter table table_name add constraint tableName_columnName_pk PRIMARY KEY (column_name);

Understand how the foreign key constraints are implemented and understand the alter table statement which does this.

alter table test1 add constraint test1_main_pk PRIMARY KEY(main);

Know that relationship tables always have a composite primary key [built from the primary keys of the participating tables] and

always has foreign keys built on both columns.

Which of the following is a key benefit of using JDBC? a. It is a simple-to-use database management system. b. Allows developers to change the underlying database without modifying the Java code. c. It is a multi-platform database management system. d. It provides a GUI for database management systems.

b. Allows developers to change the underlying database without modifying the Java code.

What will happen if you try to store duplicate data in a primary key column? a. The column is duplicated. b. An error will occur. c. The duplicate data will have concurrency issues d. The primary key column will not display the duplicate data.

b. An error will occur.

[Fig 3] Consider the following statement: Alter table owns Add constraint owns_property_owner_id_pk primary key(property,owner_id); Which of the following is true for the above statement? a. The statement is unnecessary because the owns table is not implemented. b. Builds composite primary key based on the property and owner_id fields of the owns table. c. Is syntactically incorrect - the primary key can only be built on one column d. The statement is unnecessary because of the one-to-many relationship between owns and property.

b. Builds composite primary key based on the property and owner_id fields of the owns table.

What string lists the protocol that should be used to access a database, the name of the database, and potentially other items? a. JDBC driver b. Database URL c. JDBC locator d. Database specifier

b. Database URL

What does JDBC stand for? a. Java data binary collection b. Java database connectivity c. Java database collection d. Java data basic computation

b. Java database connectivity

This type of SQL statement is used to retrieve the rows from a table. a. GET b. SELECT c. RETRIEVE d. READ

b. SELECT

CouponForPizza data fields are preceded by tildes ~ . getArea method for Shape class is in italics. Which of the following would be the class declaration for Shape? (pic1) a. public class Shape { ...} b. public abstract Shape { ... } c. public final Shape {... } d. class Shape {....}

b. public abstract Shape { ... }

How many classes have package datafields? (pic1) a. 0 b. 1 c. 2 d. 3

b. 1

How many database tables will be implemented in Fig. 1? a. 1 b. 2 c. 3 d. 4

b. 2

Does every person stored in the person table have to be related to another person stored in the person table? a. Yes b. No

b. No

Classes and interfaces for the JDBC API can be found in which package? a. java.jdbc. b. javax.jdbc. c. java.sql. d. java.sql.jdbc.

c. java.sql.

What type of class is CouponForPizza? (pic1) a. Aggregation b. Composition c. Association d. Generalization

c. Association

[Fig 3] Consider that the employee table has the following schema (The desc command displays the columns in the same order as shown below): Employee(Employee_ID,Employee_name,office_number) Assume that all Primary key/Foreign key constraints have been created on all tables as illustrated on the E-R diagram. What does the following command do? (Assume that no other rows have 1806 as the employee_id) Insert into employee Values(1806,"Jim Davis", NULL); a. Correctly inserts a new row into the employee table (no error messages displayed) b. Displays an error message (Foreign key constraint violated) but inserts the data anyway c. Displays that a NOT NULL constraint (employee_office_number_nn) was violated and does not insert the row. d. Displays an error message because no columns of any table are ever allowed to be NULL

c. Displays that a NOT NULL constraint (employee_office_number_nn) was violated and does not insert the row.

What exception is thrown if a syntax error is in the SQL query that is beining invoked by the JDBC's executeQuery method? a. NoSuchClassException b. ClassCastException. c. SQLException d. IllegalClassException.

c. SQLException

[Fig 3] Consider the following statement: Alter table employee Add constraint employee_office_number_fk foreign key(office_number) References sales_office(office_number); Which of the following is true for the above statement? a. The statement guarantees a unique identifier for the office_number column of the office table. b. The statement provides a foreign key constraint for the office_number column of the office table. c. The statement provides a foreign key constraint for the office_number column of the employee table d. Both b and c are correct

c. The statement provides a foreign key constraint for the office_number column of the employee table

What type of relationship is observed in Fig. 1? a. one-one b. one-many c. many-many d. many-one

c. many-many

Which would be the correct method header for getArea within the Shape class ? (pic1) a. public double getArea(); b. public static double getArea(); c. public abstract double getArea(); d. public double getArea() = 0;

c. public abstract double getArea();

Create table syntax

create table table_name ( field1 varchar(25) field2 numeric);

How many tables are created from the E-R diagram in Fig. 3? a. 6 b. 3 c. 4 d. 5

d. 5

[Fig 3] Consider the following statement: delete from sales_office; Assume that all Primary key/Foreign key constraints have been created on all tables as illustrated on the E-R diagram. Assume all tables are fully populated with data. Which of the following is true? a. Correctly deletes all record of office table (no error message displayed) b. Violates foreign key constraint implemented on Employee table c. Violates foreign key constraint implemented on Property table d. Both b and c are correct

d. Both b and c are correct

Which class has a static method? (pic1) a. Customer b. Order c. Pizza d. Coupon

d. Coupon

Which class has public data fields? (pic1) a. Order b. Pizza c. Customer d. Coupon

d. Coupon

What is the relationship between Circle and Shape ? (pic1) a. Aggregation b. Many-to-Many c. Composition d. Generalization

d. Generalization

What term refers to data that describes other data? a. Pseudo-data b. Micro data c. Abstract data d. Meta data

d. Meta data

[Fig 3] Consider the following statement: Insert into owns Values(1215,101,50); Where 1215 represents the property id, 101 is the owner id, and 50 is the percent owned. Which of the following statements is true: a. The property id 1215 must be the primary key for a row in the property table b. The owner id 101 must be the primary key for a row in the owner table. c. The value of 50 must be Not Null d. a and b are true

d. a and b are true

Which Statement interface method should be used to execute a SELECT statement? a. executeSQL b. executeStatement c. executeUpdate d. executeQuery

d. executeQuery

Which of the following would be the correct declaration for totalCoupons with the Coupon class ? (pic1) a. private int totalCoupons; b. private double totalCoupons; c. public static int totalCoupons; d. private static int totalCoupons = 0;

d. private static int totalCoupons = 0;

Which of the following would be the declaration for the name data field within the Shape class ? (pic1) a. private String name; b. abstract String name; c. String name; d. protected String name;

d. protected String name;

Understand Generalization and how it applies to data fields/methods

draw arrow from sub to base

Know the drop command for dropping a table:

drop table table_name;

Which of the following best describes the association between Pizza and Shape? (pic1) a. It is a 1-to-1 relationship b. It is a many-to-one relationship c. Pizza is contained within Shape d. Shape is contained within Pizza e. Both b and d are true

e. Both b and d are true

Insert a row into table syntax:

insert into table_name values('Joe Smith", '1215 Main', '329-2981'); commit;

How does the many-many cardinality affect participation constraints?

it must have a linkage/relationship table. build with the PK of each participating table

Know how to retrieve the MetaData from a ResultSet.

rmd= rs.getMetaData(); [# of and names of columns-retrieve data & for loops]

How does one-many cardinality affect foreign key constraints?

the PK of the one side can be copied to the many side as the FK

Update data syntax:

update table_name set nam='Jim Smith' where phone='329-2981';

In JDBC, understand what parameters are needed for creating a Connection object.

user(name), password, url which is found in db.properites

What are the mapping cardinality/multiplicity between entities? || |< o< o|

|| min one, max one |< min one, max many o< min zero, max many o| min zero, max one