Database Management Exam 1
How many primary keys and foreign keys can a table have?
1 primary key, multiple foreign keys
What is a logical data model?
A logical data model is a framework used to sort data records
What is a primary key?
A primary key is the unique identifier of an entity. It is always required.
The mapping cardinality between Publisher and Book is usually A) 1:m B) m:1 C) 1:1 D) m:m E) 1:0
A) 1:m
Identify Objects for Data Modeling: Which of the following are objects for the problem domain of banking systems? (check all that apply) A) ATMs, employees, branches, customers B) account balance, credit, debit, fraud C) customer service, help desk, customer satisfaction, validate accounts D) withdraws, transfers, deposits E) products, accounts, orders
A) ATMs, employees, branches, customers D) withdraws, transfers, deposits E) products, accounts, orders
What is the best entity-relationship diagram to model students, dorms, rooms for student housing? A) Dorm -- (1:m) -- Room --(1:m) -- Student B) Dorm -- (1:m) -- Room --(m:m) -- Student C) Student -- (m:1) -- Dorm --(1:m) -- Room D) Dorm -- (1:m) -- Student--(m:1) -- Room
A) Dorm -- (1:m) -- Room --(1:m) -- Student
Which Oracle server parameters do you need to know in order to set up an client to connect to the server? (check all that are necessary) A) Host Name or IP address of the server B) Oracle Serial Number C) TCP Port Number to Oracle Service D) Oracle Instance ID E) Operation Systems that Holds Oracle
A) Host Name or IP address of the server C) TCP Port Number to Oracle Service D) Oracle Instance ID
In SQL statements, constraint X foreign key (Y) references Z, what is Y? A) The name of the foreign key columns in the current table B) The name of the constraint C) The name of the table to which the foreign resides D) The name of the table from which the corresponding PK resides E) The name of primary key columns in the referenced table
A) The name of the foreign key columns in the current table
What is the difference between an ERD and a relational model (check all that apply) A) They are used in different stages of the database development B) An ERD tells how records are organized and a relational model shows data requirements C) A relational model tells how records are organized and an ERD shows data requirements D) They use different sets of concepts, terminologies, and notations E) They use unrelated sets of concepts, terminologies, and notations
A) They are used in different stages of the database development C) A relational model tells how records are organized and an ERD shows data requirements D) They use different sets of concepts, terminologies, and notations
The difference between char(100) and varcharc2(100) is that, when actual text is less than 100 characters, varchar2(100) will automatically save the remaining unused spaces for other uses. True or False A) True B) False
A) True
How do you create a sequence that can generate integers numbers 1, 5, 9, 13, ...? A) create sequence mySEQ start with 1 increment by 4; B) create sequence mySQL as 1, 5, 9, 13, ...; C) create sequence mySEQ start with 4 increment by 1; D) create sequence mySEQ start 1 and increment 4;
A) create sequence mySEQ start with 1 increment by 4;
Identify Object Relationships: Check all that justify a direct relationship between the two mentioned objects for airline reservation systems? (check all that apply) A) customers make reservations B) employees print boarding passes C) customers make payments D) passengers are assigned to airplane seats E) flights have destinations
A) customers make reservations C) customers make payments E) flights have destinations
Which of the following is true about constraint names? (check all that apply) A) it can be used for DBA to identify a constraint easily B) Two constraints can have the same name if they are located in two different tables C) All constraints must be named uniquely D) Constraint names are case sensitive
A) it can be used for DBA to identify a constraint easily C) All constraints must be named uniquely
Identify Object Attributes: Which of the following are attributes of orders in an e-commerce system? (check all that apply) A) order date B) promised date C) ship date D) total order amount E) order quantity
A) order date B) promised date D) total order amount
Identify Objects for Data Modeling: Which of the following are objects for the problem domain of inventory management? (check all that apply) A) products, warehouses, inventories, accounts B) orders, shipments, invoices, packing slips, employees, locations C) price, quantity, balance, business, services D) minimum reorder level, balance, inventory system, company E) suppliers, checkouts, check-ins
A) products, warehouses, inventories, accounts B) orders, shipments, invoices, packing slips, employees, locations E) suppliers, checkouts, check-ins
A relational database gets its name because A) records are stacked into relations B) all records are related C) records are linked by relationships, rather than pointers D) Edgar F. (Ted) Codd named it as such for no reason E) All records are called relations
A) records are stacked into relations
Identify Object Attributes: Which of the following are legitimate attributes of shipments in an online order system? (check all that apply) A) ship date B) check shipping status C) delivery address D) warehouse E) delivery date
A) ship date E) delivery date
Identify Object Relationships: Check all that justify the connection between the two mentioned objects for a student registration system? (check all that apply) A) students takes classes B) instructors teach students C) students goes to classroom D) departments offer courses E) instructors belong to departments
A) students takes classes D) departments offer courses E) instructors belong to departments
When do you use recursive relationships? (check all that apply) A) the relationship between employees and their supervisors B) the relationship between employees and their dependents C) the relationship between employees and their departments D) the relationship between employees and their team members E) the relationship between employees and their job assignments
A) the relationship between employees and their supervisors D) the relationship between employees and their team members
In the ER diagram for a Point of Sale system, there are entities such as Cashier, Product, Checkout, Transaction, CheckoutStand, Return, Inventory, etc. Here inventory records each storage location and its capacity. Assume we need to track certain products are bundled and must be sold together. Which of the following is the correct design? A) Create Bundle entity to link with Product B) Create a recursive relationship for Product C) Create Bundle as a weak entity of Product D) Create Bundle as a subtype of Product
B) Create a recursive relationship for Product
What technique is used to model the situation that Student may have multiple addresses? A) Associative Entity (gerund) B) Weak entities C) Super/sub types D) Recursive relationships
B) Weak entities
An attribute must be the following except for which one: A) a column of a table B) a record C) a field D) single-valued
B) a record
Identify Object Attributes: Which of the following are legitimate attributes of Borrower in a college library system? A) book title B) borrower type C) overdue D) checkouts E) returns
B) borrower type
A flight has a starting airport, a destination airport, and zero or more pass-over airports. How do you model the relationships between Flight and Airports? A) create three different relationships respectively for starting, destination, and passover B) create a gerund between Flight and Airport and use an attribute to indicate starting, destination, and passover C) create one direct m:m relationship between Flights and Airports D) create an recursive relationship on Airports
B) create a gerund between Flight and Airport and use an attribute to indicate starting, destination, and passover
You have run an SQL statement that asked the DBMS to display data in a table named USER_TABLES. The results include columns of data labeled "TableName," "NumberOfColumns" and "PrimaryKey." You are looking at ________ . A) user data. B) metadata C) A report D) indexes
B) metadata
Identify Objects for Data Modeling: Which of the following are objects for the problem domain of student registration? (check all that apply) A) tuition, financial services, fees, credit hours, registrar B) students, courses, instructors, sections, classrooms C) advisers, departments, buildings, registrations D) registration time, class cap, class closed, payment due date E) textbooks, parents, university, registration system, print receipt
B) students, courses, instructors, sections, classrooms C) advisers, departments, buildings, registrations
What is a multivalued attribute?
By a multiway relationship when 3 or more relationships go into one entity.
Which of the following is NOT required by the referential integrity rule #1? A) A foreign key column must have the same data type as the corresponding primary key column B) A foreign key column must have the same field size as the corresponding primary key column C) A foreign key column must have the optionality as the corresponding primary key column D) None of the above
C) A foreign key column must have the optionality as the corresponding primary key column
What SQL command will allow you to change the table STUDENT to add the constraint named GradeCheck that states that the values of the Grade column must be greater than 0? A) ALTER TABLE STUDENT ALTER CONSTRAINT GradeCheck (Grade > 0); B) ALTER TABLE STUDENT ADD CONSTRAINT GradeCheck (Grade > 0); C) ALTER TABLE STUDENT ADD CONSTRAINT GradeCheck CHECK (Grade > 0); D) None of the above is correct.
C) ALTER TABLE STUDENT ADD CONSTRAINT GradeCheck CHECK (Grade > 0);
What SQL command can be used to delete columns from a table? A) MODIFY TABLE TableName DROP COLUMN ColumnName B) MODIFY TABLE TableName DROP ColumnName C) ALTER TABLE TableName DROP COLUMN ColumnName D) ALTER TABLE TableName DROP ColumnName
C) ALTER TABLE TableName DROP COLUMN ColumnName
Referential integrity rules allow which of the following? A) FK column has different data type from the corresponding PK column B) FK column has different data size from the corresponding PK column C) FK column has different name from the corresponding PK column D) FK column has data not in the corresponding PK column
C) FK column has different name from the corresponding PK column
What a logical data model is used for: A) For representing data requirements B) For representing business logics C) For representing how data is organized in a database D) For representing how functions are performed
C) For representing how data is organized in a database
How do you convert a recursive m:m relationship into relational tables? A) duplicate the primary key twice inside the same table B) duplicate the primary key once inside the same table C) create a junction table and duplicate the primary key twice in the junction table D) create a junction table and duplicate the primary key once in the junction table
C) create a junction table and duplicate the primary key twice in the junction table
Which of the following may use check constraints? (check all that apply) A) primary key B) foreign key C) domain validation rules D) Business rules E) Arc implementation
C) domain validation rules D) Business rules E) Arc implementation
What is the command to remove table xyz and also any constraints that are attached to it? A) drop table xyz; B) delete from xyz; C) drop table xyz cascade constraints; D) delete from xyz where count(*) >=1;
C) drop table xyz cascade constraints;
What is not correct about a record? A) it is made of many values observed from one entity B) it is usually linked to other records C) it is made of a single value D) it is also called a tuple E) It is usually identifiable through some parts of the record
C) it is made of a single value
What is not right about weak entities? A) often used to model multivalued attributes B) do not have a full UID themselves C) must have 1:m relationships with a strong entity D) cannot exist independently of a strong entity
C) must have 1:m relationships with a strong entity
What is not true about a hierarchical database? A) records are linked by pointers B) records are organized as an inverted tree C) records are not identified by keys D) it is not a dominant type of databases nowadays
C) records are not identified by keys
Which is not a relevant feature of CASE tools? A) The ability to help draw data models using entity-relationship notations B) The ability to generate code C) An information repository D) Access to a DB via the Internet
D) Access to a DB via the Internet
In the ER model for a library checkout system, the involved entities include Book, BookCopy, Patron, Checkout, Return, Reservation, Employee, and Transaction. Assume each return is for many books. If a book is returned with damage, where do you record the information so that we can track the damage by user in case we need to? A) Book B) BookCopy C) Patron D) Association between Return and BookCopy E) Return
D) Association between Return and BookCopy
Identify Object Relationships: Check all that justify a direct relationship between the two mentioned objects in each sentence for a point of sale system (POS)? (check all that apply) A) cashiers checkout items B) managers balance inventories C) customer check price D) cashiers performs transactions E) employee handle returns
D) cashiers performs transactions E) employee handle returns
How is a gerund converted into a relational model? A) ignore the gerund in the conversion B) convert the gerund into a table without primary key C) Convert the gerund into a table duplicate the primary keys of its both ends as a part of the composite foreign key for the gerund table D) convert the gerund into a table and duplicate the primary keys of its both ends as a part of the composite primary key for the gerund table
D) convert the gerund into a table and duplicate the primary keys of its both ends as a part of the composite primary key for the gerund table
In the relational model, relationships between relations or tables are created by using: A) composite keys B) determinants C) candidate keys D) foreign keys
D) foreign keys
Based on the following SQL script, which command is correct to change the size of title to varchar2(50)? create table books ( ISBN char(15), title varchar2(100), price number(4,2) null, constraint pk_bk primary key (ISBN)); A) alter table books modify (title varchar2(50)); B) alter table books alter (title varchar2(50)); C) alter table books modify (title char(50)); D) alter table books alter (title char(50)); E) none of the above
E) none of the above
How do you handle dependents of an employee in a database?
Employee is the strong entity with a primary key of empID. The dependents would have their own table as a weak entity, taking on the empID.
When do you use associate entities/gerunds?
Gerunds are used because many to many relationships can't be used in a relational database, so the gerund is used to break it down into tow one to many relationships.
What are the benefits of super/subtypes?
It allows you to simplify an ERD.
What is a foreign key?
It is a primary key that we have copied from another table.
When do you use a recursive relationship?
It is used to connect the entity to itself.
What are exclusive relationships?
It is when an entity can't participate in all relationships.
How are tables, rows, and columns of a relational model displayed in an ERD?
Tables are relationships, rows are the entity, and columns are attributes.
How are records that are related tracked in a hierarchal model?
They are tracked via pointers.
Why should recursive relationships always be optional?
This is due to max cardinality.
When do you use weak entities?
Weak entities are used when there is a many to one relationship with a strong entity. The weak entity cannot exist without the strong entity. (building in an apartment*)
What is an Oracle dictionary table?
Where your most important info is contained/metadata
How would you handle inpatients and outpatients in a hospital database?
You would use supertypes and subtypes. The supertype would be visitors and the patient types would be subtypes.
How do you look up constraints you have in your table?
select constraint_name from user_constraints where table_name = 'CLASSES';
What is the SQL command for looking up something in your table?
select table_name from user_table;
