222 Final Exam
How many row(s) are required to hold information specific to each entity?
1
Security
A database's ___________________ features help to ensure that there is no accidental/intentional deletion of records from the database.
Entity
A distinct object in a database is a/an _______________.
Denormalization
A good design can achieve a balance between accuracy and performance with a little _________________ at the end of the design process.
What is a flat file?
A large table containing a lot of information
one-to-many
A many-to-many relationship can be resolved into a resolution entity with two _______________ relationships for clarity.
Design mode
A method of interacting with the database directly.
unique constraint
A one-to-one relationship needs both a foreign key and a ______________ on the foreign key for physical implementation.
trigger
A program code that fires when a specific action occurs.
Constraint
A set of allowable values for one or more attributes.
All of the following are relationship types except:
A. 1-1 B. 1-M C. 2-2 D. M-N Answer: C
Columns without _________ are considered null.
A. Attributes B. Data C. Operators D. Row Answer: B
Which is a disadvantage of the DBMS?
A. Complexity B. Cost of conversion C. A & B D. None of the above Answer: C
Connecting two pieces of text is called _________/
A. Concatenation B. Null value C. String D. Union Answer: A
Which of the following is an advantage of the DBMS?
A. Control of data redundancy B. Sharing of data C. Data consistency D. All of the above Answer: D
This represents an object or an activity:
A. DML B. Attribute C. Primary Key D. None of the above
Who is responsible for the physical design of the database as well as its security and integrity control?
A. End User B. Application programmers C. Database Administrator D. Database Designer Answer: C
This is not a building block of the relational database:
A. Entity B. Attribute C. Entity set D. None of the above Answer: C
Which types of constraints ensure that each row in a table is unique? Select two
A. Foreign key B. Primary key C. Unique key D. Check constraint Answer: B & D
The following are all relational keys except:
A. Foreign keys B. Secondary keys C. Primary keys D. Tertiary keys Answer: D
The four DML basic operations are:
A. Insert, delete, update, select B. Delete, join, update, insert C. Union, update, select, add D. All of the above Answer: A
How does defining constraints help in designing databases?
A. It increases security B. It reduces human error C. It simplifies data input D. It improves database accuracy Answer: B
Which is not an entity?
A. Jack Hammond B. Lancaster C. Thaddeus Stevens College of Technology D. Sophomore students Answer: A
In a client-server environment which of the following is not a function of the client:
A. Manages the user interface B. Accepts and checks the syntax of the user input C. Passes response back to the user D. All of the above Answer: D
Which is not part of the system catelogue?
A. Names of network B. Names of authorized users C. Names of relationships D. Names, types and sizes of data items Answer: A
All except __________ is a major component of a DBMS:
A. Query processor B. DBMS Checker C. Database manager D. File Manager Answer: D
To ensure that proper values are entered into a database, one could use:
A. Roles B. Privileges C. Constraints D. SQL Answer: C
Which is another term for record?
A. Row B. Tuple C. Relation D. Both A & B E. A, B and C are terms for records Answer: D
All of the following are characteristics of relations except?
A. The relation has a name that is distinct from all other relation names B. Each attribute has a distinct name C. The order of the attributes is very important D. Each cell in the relation contains one atomic value Answer: C
Where will the names of authorized users of the database be stored?
A. User Table B. System Catalog C. Master Database D. None of the above Answer: B
Some of the functions of a DBMS are:
A. Voice recognition B. Transaction support C. Data storage, retrieval and update D. A user-accessible catalog Answer: All 4
Which of the following is a valid condition?
A. WHERE 'Smith' B. WHERE Job_description = 'Manager' C. WHERE salary '>10000' D. WHERE LAST_NAME BETWEEN 'K' AND '9' Answer: B
The number of users that are allowed to access a database simultaneously is _________.
A. fixed B. dependent on the database design C. Unlimited D. One Billion Answer: B
Temporary table takes place on client Reduces network traffic Operations can be done on a specific table Data can be loaded in temporary table before modifying it
Advantages of temporary tables
Concurrency
Allowing Many users to access the same file (database object) simultaneously.
transaction-initiating
An SQL transaction automatically begins with a ______________________ SQL statement.
What are the major characteristics of a DBMS
An integrity system A data manipulation language A data definition language A concurrency system
software application
Anomalies usually have to be caught and corrected in the ____________ that uses the database
Data quality is ensured by using ________________.
Constraints
Columns without __________ are considered null.
Data
SQL is an industry-standard language for querying ______________.
Databases
The number of users that are allowed to access a database simultaneously is _____________.
Dependent on the database design
What are the 3 major limitations of a file based system?
Duplication of data Incompatibility of Files Separation and isolation of data
Use # prefixed to the name of the table
How do you set up a temporary table?
three
If a database is not normalized at least to ____________________ normal forms, then there are chances of incomplete data insertions.
saved state, resubmitted
If a transaction fails during execution, then the database will not be in a ____________. All the changes that had been made must be ___________.
fourth
Implementation of the ________________ and fifth normal forms depend on whether the business rules require them.
Table
In a database, information about a single category of items is always stored in the form of a ______________.
What is the most important thing to remember about information contained in tables?
It is information about a single category of items
Normalized
It is necessary for a database to be ________________ to provide greater accuracy while editing data.
Read-only
Most data warehouses are designed to be _______________ and therefor do not need normalization.
parent
One of the most common forms of denormalization in a database is to include summary data in the _________________ entity.
multivalued
One single column or attribute group that repeats in the database is sometimes called a ___________________ attribute.
Relationships
Records in one table can be connected to relevant records in other tables by defining ____________________.
Enforce data integrity Consistent implementation of complex business rules and constraints Modular design Maintainability reduced network traffic Faster execution Security enforcement
Role of stored procedures in DB apps
COMMIT, ROLLBACK
SQL defines transaction model based on _______ and __________.
attributes
Some anomalies occur due to incorrect use of optional relationships and _________________, as well as from redundant data.
Insert, update, and delete
The 3 different kinds of triggers
COUNT, SUM, AVG, MIN, MAX
The 5 main aggregate functions
Subselect
The _________ is known as the defining query.
null
The columns in the foreign key, in an optional relationship, can have _________ values.
View
The dynamic result of one or more relational operations operating on the base relations to produce another relation. A view is a virtual relation that does not necessarily exist in the database but can be produced upon request by a particular user at the time of request.
Redundancy
The efficiency and accuracy of a database can be ensured by eliminating ______________________ of data.
Degree
The number of attributes in a relation.
deletion anomalies
The result of deleting data, in a way that other data is also lost, is called ________________.
insert anomaly
The result of entering incomplete data into a database is a(n) __________________.
normal forms
The rules that ensure normalization of databases are referred to as ___________________.
key
The rules to apply the first three normal forms are summarized as "Each attribute must be a fact about the whole_________________."
database
The shared collection of logically related data and it's description, designed to meet the information needs of an organization.
Data mode
The sub-language that is used to specify the database schema.
User-defined System Extended Temporary Global Temporary Remote CLR (Common Runtime Language) - handles execution of managed application code
Types of stored procedures
redundant data
Update anomalies generally occur when there is __________________.
Automates repetitive tasks Server generates and maintains an execution Execution plan is stored as part of database, when used there is no need for the system to regenerate execution plan. (Executes efficiently) Ability to grant permission for execution
What are the advantages of stored procedures?
Data independence Currency Improved security Reduced complexity Convenience Customization Data integrity
What are the advantages of views?
Hardware (PCs, servers, etc.) Software (DBMS operating system, network operating system and any application programs) Data Procedures (Instructions and rules that should be applied to the design and use of the database and DBMS) People
What are the components of the DBMS environment?
Boolean Character Bit Exact Numeric Approx. Numeric Datetime Interval Large Object
What are the different SQL data types?
Required data Domain constraints Entity integrity Referential integrity General constraints
What are the different types of integrity constraints?
Update restriction Structure restriction Performance
What are the disadvantages of views?
Max name length is 128 characters May contain up to 2100 input/output characters Max size of body is 128 MB.
What are the limits of stored procedures?
Domain
What defines what kind of value an attribute can contain?
CHAR data has a fixed length, and when information doesn't meet the length requirement blank are added to the right of the input to match the requirement. VARCHAR can have varying length strings, which uses less space.
What is the difference between CHAR and VARCHAR?
Buffer to hold intermediate table data in a PL/SQL statement Held in memory until it disconnects from the server Write transaction does not apply Generally accessible only to the user who created it
What reasons are there for having a temporary table?
entity
When the first normal form rule is applied, each repeating group of attributes is separated into another _______________.
size
While choosing a database engine, the ______________ of the database is the deciding factor.
View resolution
With ______________, any operations on view are automatically translated into operations on relations from which it is derived.
View materialization
With _______________, the view is stored as a temporary table, which is maintained as the underlying base tables are updated.
Database
______________ integrity is a constraint that states that in a base relation no attribute of a primary key can be null.
Tuple
a row in a table that contains data about a specific item in a database table
Why is a relational database so called?
it relates to records
In a database with a large number of users, privileges are grouped in the form of _________ to assign a set of privileges to a certain category of users.
roles