DS220 PSU Exam #1
Which is a binary comparison operator?
!=
Which is a binary Arithmetic Operator?
%
Our library system has several branches. Each branch has an address and a unique code. Each book belongs to a particular branch. Each book is assigned a unique barcode number and has a title and a publisher name. Each library member may borrow up to three books at a time. To join the library, a member provides an identification number and identification type, such as driver's license, passport number, or employee number. The entities are Branch, Book, and Member. What are the Attributes of the Book entity? _________ (Answer in format: x, y, z). Please use all lower case letters for attributes!
barcode number, title, publisher name
Can a query select one specific row from a table?
Yes, by specifying one or more row values.
Our library system has several branches. Each branch has an address and a unique code. Each book belongs to a particular branch. Each book is assigned a unique barcode number and has a title and a publisher name. Each library member may borrow up to three books at a time. To join the library, a member provides an identification number and identification type, such as driver's license, passport number, or employee number. The entities are Branch, Book, and Member. What are the Attributes of the Branch entity? ___________ (Answer in format: x, y, z). Please use all lowercase in attribute name.
address, branch code
The Widgets & Digits company has projects that are composed of multiple tasks. Projects have a limited duration, and tasks have a starting date. How would the following item in the requirements map to ER model concepts? The duration of a project is a(n) ______ __________. Please use all lower case!
attribute
SQL commands can create databases and tables. True False
True
The primary key of a subtype table is also a foreign key. True False
True
The subtype table primary key is identical to the supertype table primary key. True False
True
What is the value of the following expression?: (Age >= 13 AND Age <= 18) OR Military = 'Army' where Age = 8 and Military = 'Army' True False
True
Which statement must precede a SHOW TABLES statement to see the tables from the bikeStore database?
USE bikeStore;
Standard attribute types are determined after the ER diagram is drawn. always, usually, or never
usually
What is the result of a relational operation?
A table
Which is a unary or binary Arithmetic Operator?
-
Which statement is NOT CORRECT? A. In a conceptual data model, the data requirements from the business should be captured and modeled. B. A conceptual data model is implementation dependent. C. A logical data model translates the conceptual data model to a specific implementation environment. D. Examples of implementations of logical data models are hierarchical, CODASYL, relational or object-oriented models.
B
Which statement is NOT CORRECT? A. In a database approach, applications don't have their own files, but all applications access the same version of the data by interfacing with the DBMS. B. In a database approach, the data definitions or metadata are stored in the applications accessing the data. C. In a database approach, there is typically less storage needed compared to the file approach. D. In a database approach, maintenance of data and metadata is easier.
B
The INSERT statement adds a student to the Student table. How many clauses are in the INSERT statement? INSERT INTO Student VALUES (888, 'Smith', 'Jim', 3.0);
2
A table with a simple PK must be in which normal from (NF)?
2NF or higher
Which statement is NOT CORRECT? A. ACID stands for Availability, Consistency, Isolation and Durability. B. Durability means that effects of each successful transaction can be made permanent. C. Consistency assures that the database is consistent at all times. D. Isolation ensures that the effect of concurrent transactions should be the same as if they had been executed in isolation.
A
Statement A: Physical data independence implies that neither the applications, nor the views or logical data models must be changed when changes are made to the data storage specifications in the internal data model.Statement B: Logical data independence implies that software applications are minimally affected by changes in the conceptual or logical data model.Which sentence(s) is/are right?
A and B are right
An entity-relationship model is developed for all database design projects. True False
False
Data in a relational database can violate relational rules. True False
False
Entities, relationships, and attributes always map directly to tables, foreign keys, and columns, respectively. True False
False
Which statement is CORRECT? A.The recovery manager keeps track of all the database operations in a logfile. B. The connection manager sets up a database connection and verifies the logon credentials and the privileges. C. The query rewriter optimizes the query based on the current database state. D. The lock manager is responsible to ensure the ACID properties.
A.
Complete the following sentence, choosing the right words on place A and B.When during crash recovery, aborted transactions need to be undone, that is a task of the ...A...The part of the Storage manager that guarantees the ACID properties is the ...B...
A. recovery manager, B. Transaction manager
Which combination of operations are NOT all relational operations? A. Select, project, intersect, union B. Intersect, difference, integrate, product. C. project, intersect, join, product D. rename, aggregate, union, project
B
Which of the following statements about OCL is not correct? A. Pre- and post-conditions on methods must be true when a method either begins or ends. B. OCL can be used to specify various types of constraints in a procedural way. C. A class invariant is a constraint that holds for all objects of a class. D. Role names of associations can be used to navigate between classes.
B
Which statement is CORRECT? A. The database state represents the data in the database when the database is first created. B. The database state changes when data is updated or removed. C. The database state specifies the various data items, their characteristiscs and relationships and is specified during the database design. D. The database is stored in the catalog.
B
In a 1NF table, non-key columns depend only on the PK. True False
False
The Logical design phase ends when attributes have been implemented as columns. True False
False
Which statement is CORRECT? A. It is more cumbersome to query a database system than a file-based system. B. The file-based approach uses metadata to define the data. These definitions are then stored in a catalog. C. A database approach to data management uses the available storage in a more efficient manner. D. The maintenance of a file-based system is much easier than the maintenance of a database system.
C
Which statement is NOT CORRECT? A. In a file-based approach, every application had its own query and access procedures, even if they wanted to access the same data. B. SQL is a database language to manage DBMSs without having to write a substantial amount of programming code. C. SQL is a database language that focusses on how to access and retrieve the data. D. SQL is a database language that allows different applications to access different subsets of the data necessary for each application.
C
The foreign key in a subtype table implements the composite relationship between subtype and supertype entities. True False
False
The foreign key in the subtype table usually has the referential integrity action restrict on primary key delete. True False
False
The rule that data may not be repeated in a specific column is a relational rule. true false
False
Our state's Department of Education maintains a database of colleges in the state. Each college has a name, a code, and the count of students currently enrolled. Each college may have more than one campus. Each campus has a name that is unique within the college and has an address. More than one campus of a college may be located in the same city. Cities are identified by name. A city may contain campuses of more than one college. The entities are College, Campus, and City. What are the relationships? __________________ (Answer in format: x, y, z). Please follow naming conventions for relationships.
Campus-IsLocatedIn-City, College-Has-Campus
The rule that different columns of a table must have different names is business rule. True False
False
What's the value of the Expression: (8 % 3 + 10 > 15) AND TRUE True False
False
Which of these is not a part of the Storage Manager in the DBMS Architecture?
Connection Manager
Databases may be categorized by: A. number of users permitted B. data models C. type of use D. All of the above answers are correct
D
Which statement is CORRECT? A. A class is an instance of an object. B. A class only has variables. C. Inheritance is not supported in Object-Orientation. D. Information hiding (also referred to as encapsulation) states that the variables of an object can only be accessed through either getter or setter methods.
D
Which statement is not correct? A. The file approach to data management causes the same information to be stored separately for different applications. B. In a file approach to data management, the data definitions are included in each application separately. C. In a file approach to data management, different applications could be using older and newer versions of the same data. D. In a file approach to data management, a change in the structure of a data file is easily handled because each application has its own data files.
D
handles: Grant all permissions to user 'tester'.
DCL - Data control language
[ Select ] handles: Create table Product.
DDL - Data Definition Language
handles inserting a row into table Product.
DML - Data manipulation language
Which of these is part of the Query Processor in the Architecture of a DBMS?
DML Compiler
[ Select ] handles selecting all rows from table Product
DQL - Data Query Language
[ Select ] handles rolling back database changes.
DTL - Data Transaction Language
When we talk about databases that are relational (such as many SQL databases), hierarchical, or NoSQL, we are categorizing based on:
Data Model
What concept specifies the various data items, their characteristics and relationships, constraints, storage details, etc. and is specified during the database design?
Database model
Adding a FOREIGN KEY constraint to a table only affects inserting new rows into the table. True False
False
All database systems (DBMSes) use identical SQL statements. True False
False
An SQL statement can implement only one relational operation. True False
False
Refer to the table definition below. CREATE TABLE Department ( Code TINYINT UNSIGNED AUTO_INCREMENT, Name VARCHAR(20) NOT NULL, ManagerID SMALLINT UNSIGNED, PRIMARY KEY (Code)); Which statement correctly inserts an unnamed department with no manager?
INSERT INTO Department (Name, ManagerID) VALUES ('', NULL);
Refer to the table definition below. CREATE TABLE Department ( Code TINYINT UNSIGNED AUTO_INCREMENT, Name VARCHAR(20) NOT NULL, ManagerID SMALLINT UNSIGNED, PRIMARY KEY (Code)); Which statement correctly inserts Engineering?
INSERT INTO Department (Name, ManagerID) VALUES ('Engineering', 2538);
Refer to the table definition below. CREATE TABLE Department ( Code TINYINT UNSIGNED, Name VARCHAR(20), ManagerID SMALLINT UNSIGNED ); Which statement correctly inserts Marketing?
INSERT INTO Department (Name, ManagerID, Code) VALUES ('Marketing', 6381, 12);
Refer to the table definition below. CREATE TABLE Department ( Code TINYINT UNSIGNED, Name VARCHAR(20), ManagerID SMALLINT UNSIGNED ); Which statement correctly inserts Engineering?
INSERT INTO Department(Code, Name, ManagerID)VALUES(44,'Engineering', 2538);
Refer to the statement below. CREATE TABLE Department ( Code TINYINT UNSIGNED NOT NULL, Name VARCHAR(20), ManagerID SMALLINT ); Which alteration to the CREATE TABLE statement prevents ManagerID from being NULL?
ManagerID SMALLINT NOT NULL
Which is a unary logical operator?
NOT
Refer to the statement below. CREATE TABLE Department ( Code SMALLINT UNSIGNED DEFAULT 1000, Name VARCHAR(20), ManagerID SMALLINT ); Which alteration to the CREATE TABLE statement sets the default Name to Accounting?
Name VARCHAR(20) DEFAULT 'Accounting',
Refer to the statement below. CREATE TABLE Department ( Code TINYINT UNSIGNED NOT NULL, Name VARCHAR(20), ManagerID SMALLINT ); Which columns may contain NULL values?
Name and Manager ID
Statement A: The middle layer of the three-layer architecture consists of both the conceptual data model and the logical data model. The logical data model is physically implemented in the internal layer.Statement B: The top level of the three-layer architecture is the external layer. Views for one or more applications always offer a window on the complete logical model.Which sentence(s) is/are right?
Only sentence A is right
What was the initial impediment to commercial adoption of relational databases in the early 1980s?
Processing speed
Delete cascade from the primary key table to the foreign key is an example of a relational rule. True False
True
In a FOREIGN KEY constraint, data types of the foreign key and primary key columns must be the same. True False
True
Which of the following DBMSs have a query processor?
Relational
In a FOREIGN KEY constraint, parentheses are required around the foreign key column name. True False
True
Data Manipulation Languages (DML) can be categorized as procedural or declarative. The difference is that declarative DMLs:
Specify only which data the user wants and leaves the details of how to find it up to the DBMS.
Given the following ER model, Which statement is NOT CORRECT?
The ER model has both weak and existence dependent entity types
What is wrong with the SQL statement below? SELECT Gpa --FROM Student;
The FROM clause is hidden as a comment
In relational theory, all tables are in 1NF. True False
True
Plural attributes are implemented as weak entities. True False
True
Refer to the statement below. CREATE TABLE Department ( Code TINYINT UNSIGNED NOT NULL, Name VARCHAR(20), ManagerID SMALLINT ); What happens when a user attempts to insert a new department without a Code value?
The database rejects the insert
The SQL statement below is used to select students with the last name "Smith". What is wrong with the statement? SELECT FirstName FROM Student WHERE LastName = Smith;
The literal "Smith" should be surrounded by single or double quotes.
What does the principle of data independence state?
The result of a database query is not affected by the physical organization of data on storage devices.
This database component is responsible for correct and efficient storage of data, which includes managing: the placement of data into physical storage, deciding which data stays in memory (buffer), managing data locks for concurrency control, and recovering from crashes
The storage manager
Given the following EER specialisation: According to the model, which of the following statements is CORRECT?
There are certain supermarket products that are not fruits and vegetables, not meat and not non-food.
What is wrong with the SQL statement below? SELECT FirstName from Student
There is a missing semi-colon to terminate the statement.
The relational model was originally developed for which types of applications?
Transactional applications like super market checkout points and banking
A UNIQUE and NOT NULL primary key is an example of a relational rule. True False
True
An attribute name indicates the data type of the corresponding column. True False
True
Are these sets the same? { (8, mango, FALSE), (-11, watermelon, FALSE) }{ (-11, watermelon, FALSE), (8, mango, FALSE) } True False
True
Assume the database system (DBMS) currently contains only one user database, called 'university', in addition to system databases such as 'mySQL'. The statement CREATE DATABASE auto; creates a database called 'auto'. True False
True
Assume the database system (DBMS) currently contains only one user database, called 'university', in addition to system databases such as 'mySQL'. The statement CREATE DATABASE university; creates a second database called 'university'. True False
True
Assume the database system (DBMS) currently contains only one user database, called 'university', in addition to system databases such as 'mySQL'. The statement DROP DATABASE university; deletes the university database and all associated tables. True False
True
The Widgets & Digits company has projects that are composed of multiple tasks. Projects have a limited duration, and tasks have a starting date. How would the following items in the requirements map to ER model concepts? Please use all lower case letters! A project is a(n)
entity
Our business has a number of factories. Each factory has a name and an address. Each factory makes several products. Each product has a product ID, name, and description. Many kinds of parts are used in manufacturing each product. Each kind of part is identified by number. Each part has a bin number indicating where the part is stored. What are the entities?: ____________________ (Answer in format: x, y, z). Please use all lower case letters for entities!
factory, product, part
Our library system has several branches. Each branch has an address and a unique code. Each book belongs to a particular branch. Each book is assigned a unique barcode number and has a title and a publisher name. Each library member may borrow up to three books at a time. To join the library, a member provides an identification number and identification type, such as driver's license, passport number, or employee number. The entities are Branch, Book, and Member. What are the Attributes of the Member entity? _________ (Answer in format: x, y, z). Please use all lower case letters for attributes!
identification number, identification type
Complete the following sentence, choosing the right words on place A and B.A ...A... data model is the mapping of a ...B... data model to a model that describes which data is stored where and in what format.
internal, logical
The Widgets & Digits company has projects that are composed of multiple tasks. Projects have a limited duration, and tasks have a starting date. How would the following items in the requirements map to ER model concepts? "Task belongs to project" is a(N) _________ _______
relationship
