Database 1
Relation Degree
# of attributes (columns)
Relation Cardinality
# of tuples (rows)
An athletics meeting involves several competitors who participate in a number of events. The database is intended to record who is to take part in which event and to record the outcome of each event. As results become available the winner attribute will be updated with the cid of the appropriate competitor. Identify the result of the following SQL statement: SELECT eid FROM Competes, Competitor WHERE Competes.cid=Competitor.cid AND nationality = 'Swedish'
02
The network model (CODASYL) was released in what year?
1969
In relational algebra the UNION of two sets (set A and Set B). This corresponds to
A OR B
Attribute
A characteristic of data (column)
An entity type is:
A coherent set of similar objects that we want to store data on (e.g. STUDENT, COURSE, CAR)
Null Constraint
A constraint that specifies that a column may have empty cells in some or all rows.
In strict relational terminology, an attribute is
A field
Schema
A formal description of all database relations and all the relationships existing between then
An athletics meeting involves several competitors who participate in a number of events. The database is intended to record who is to take part in which event and to record the outcome of each event. As results become available the winner attribute will be updated with the cid of the appropriate competitor. Competitor(cid, name, nationality) Event(eid, description, winner) Competes(cid, eid)
A numeric attribute should be added to the Competes table
A timetable database is required for a University Department. Each taught event is part of a module, each event will have exactly one member of staff associated and several individual students. Each event takes place in a single weekly time slot. Each time slot has a day of the week and a time of day associated.Each of the weekly time slots is exactly one hour long, however we wish to represent the fact that some events take more than one hour. Which of the following does not represent a possible solution.
A one-to-many relation between Events and Time-Slots is established
An instance is:
A particular occurance of an entity (e.g. Tom Osman is an instance of the entity STUDENT)
Foreign Key
A primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables
SQL was developed as an integral part of
A relational database
Relational Algebra
A set of operator to manipulate relations
Relation
A set of ordered pairs (heading and body)
Database Management System
A set of software tools that control access, organize, store, manage, retrieve and maintain data in a database (Think of MySQL)
Candidate key/unique key
A single column or set of columns in a table of a database that can be used to uniquely identify any database record without referring to any other data.
Developer ANJU executes the following statement: CREATE TABLE animals AS SELECT * from MASTER.ANIMALS; What is the effect of this statement?
A table named ANIMALS will be created in the ANJU schema with the same data as the ANIMALS table owned by MASTER.
Which of the following is NOT a type of SQL constraint?
ALTERNATE KEY
What type of join is needed when you wish to return rows that do have matching values?
All of the above (Equi-join, Natural join, Outer Join)
To transform a relation from first normal form to second normal form we must remove which one of the following?
All partial-key dependencies
To transform a relation from second normal form to third normal form we must remove which one of the following? All partial-key dependencies All inverse partial-key dependencies Incorrect All repeating groups All transitive dependencies
All transitive dependencies
Domain Constraint
All values in each column must be from the same predefined domain
Which of the following statements best decribes the function of an entity relation model?
An ER model provides a view of the logic of the data and not the physical implementation
Which of the following statements best describes the function of an entity relation model? An ER model is concerned primarily with a physical implementation of the data and secondly with the logical view An ER model is concerned primarily with a logical view of the data and secondly with the physical implementation An ER model provides a view of the logic of the data and not the physical implementation An ER model is entirely concerned with modelling the physical implementation
An ER model provides a view of the logic of the data and not the physical implementation
rounded rectangle graphic This symbol represents:
An entity
Tuple
An ordered set of value that describe data characteristics at one moment in time (row)
A characteristic of data, a real-world data feature modeled in the database is called:
Attribute
The main aspects of a relational data model
Attribute (column) Tuple (row) Domain Relation (table) Schema Keys
Project
Builds another relation by selecting a subset of attributes of an existing relation, duplicate tuples are eliminated
The following SQL is which type of join: SELECT CUSTOMER_T. CUSTOMER_ID, ORDER_T. CUSTOMER_ID, NAME, ORDER_ID FROM CUSTOMER_T,ORDER_T ;
Cartesian Join
A UNION query is which of the following?
Combines the output from multiple queries and must include the same number of columns.
Join
Concatenates two relations based on a joining condition or predicate
A relational database query normally returns many rows of data. But an application program usually deals with one row at a time. Which of the following is used to bridge this gap allowing the program to deal with on row at a time.
Cursor
The role that is responsible for the maintenance, performance, integrity, and security of a database is.
DBA (Database Administrator)
A Database Language Concerned With The Definition Of The Whole Database Structure And Schema Is ________
DDL
DELETE syntax example
DELETE FROM myTable WHERE col1 > 1000
Consider the table STUDREC, Which field (attribute) is transitively dependent on the key (SREF)
DISTANCE (how far from The Northern College does the student live)
The language used application programs to request data from the DBMS is referred to as the
DML
The role which is responsible for gathering and documenting requirements, developing an architecture, sharing the architecture with business users and management, creating and enforcing standards, defining SLA's, and preparing high level documents is known as:
Database Architect
Which of the following is not listed in the evolution of database management systems: Distribution Data Independence Integration Federation None of the above
Distribution
Division/Divide
Divides a relation R1 of degree (n+m) by a relation R2 of degree m and produces a relation degree n
A set of atomic values that are all of the same type is called:
Domain
A given relation is known to be in third normal form. Select the statement which can be inferred from this: All attributes contribute to the primary key Each non-key attribute determines the primary key Each non-key attribute is determined by the primary key Every determinant is a candidate key The relation is not in fourth normal form.
Each non-key attribute is determined by the primary key
If we write a program in a programming language and switch to SQL when we require to use the database, then the SQL environment in use is know as _______ SQL.
Embedded
Semantic Integrity Constraint
Enforces the correctness of the data
Which of the following statements BEST describes data integrity: - ensures that data entered into the database is accurate, valid, and consistent. - Rules that ensure that no primary key is allowed to accept null values - Ensures consistency between the primary key and foreign key of relations - Ensure that primary keys are unique identifiers
Ensures that data entered into the database is accurate, valid, and consistent.
The Entity Relation Model models
Entities and Relationships
The following SQL is which type of join: SELECT CUSTOMER_T. CUSTOMER_ID, ORDER_T. CUSTOMER_ID, NAME, ORDER_ID FROM CUSTOMER_T,ORDER_T WHERE CUSTOMER_T. CUSTOMER_ID = ORDER_T. CUSTOMER_ID
Equi-join
A NULL value is treated as a blank or 0.
False
A foreign key must always be a candidate key.
False
A physical view represents how the users view the data.
False
According to the entity integrity constraint, primary keys can take on any value from the set {null, 0 ... infinity}
False
Foreign keys are not necessary in relational databases.
False
Foreign keys uniquely identify records.
False
If you need to add a new field to a database, you would use the data manipulation system.
False
It is impossible to represent a one-to-one relationship in a relational schema.
False
Relational algebra provides a notation for formulating the definition of the desired relation in terms of the given relations.
False
T/F - A candidate key cannot also be the primary key of a relation.
False
T/F - A database may have numerous physical views.
False
T/F - A primary key does not have to be unique.
False
T/F - A primary key must consist of one and only one field.
False
T/F - Integrity constraints are rules that help ensure the quantity of the information.
False
T/F - It is impossible to represent a one-to-one relationship in a relational schema.
False
T/F - One of the tasks of a relational database management system (RDBMS) is to ensure that relational schemas are in at least 3NF.
False
T/F - The entity integrity constraint says that if a relation R2 includes a foreign key FK matching the primary key PK of other relation R1, then every value of FK in R2 must either be equal to the value of PK in some tuple of R1 or be wholly null.
False
T/F - The main purpose of the information model is to inform software developers and provide protocol-specific constructs.
False
T/F - The primary key does not have to be unique in relations that have a one to many relationship.
False
T/F - The surrogate key provides a unique alternative for using a foreign key that is used to save space.
False
T/F - There can be duplicate primary keys in the same file in a database.
False
T/F - Under a relational database system, if tableR has a foreign key constraint referencing S, then each tuple in R is necessarily related to some tuple in S via the foreign key.
False
T/F - We know that table Q has only one candidate key. Q is in 2NF.
False
The data definition subsystem includes security management facilities.
False
The intersection two relations R1 and R2 will result in the set of all tuples t that belong to either R1 or R2
False
The surrogate key provides a unique alternate for using a foreign key that is used to save space.
False
True/False: ODBC can be directly used with Java because it uses a C interface.
False
Embedded SQL is which of the following?
Hard-coded SQL statements in a program language such as Java.
What is the difference between an Information model and a Data model?
IM - abstract representation of entities, properties, and relationships DM - Concrete model for implementation
The first hierarchial DBMS was ____ and was released by IBM in 1968?
IMS (Information Management System)
The first hierarchical DBMS was ____ and was released by IBM in 1968?
IMS (Information Management System)
Which of the following is generally a benefit of normalisation? Performance is improved Insertion anomalies are avoided Correct Selection anomalies are avoided Number of tables is reduced
Insertion anomalies are avoided
A lack of normalisation can lead to which one of the following problems
Insertion problems
In the evolution of database management systems, in which stage would pureXML be?
Integration
Which of the following statements is true concerning subqueries?
Involves the use of an inner and outer query.
A unique KEY field
Is a special field that identifies a particular record in a data table
A unique KEY field...
Is a special field that identifies a particular record in a data table
Consider the table (Relation) CLASSES, Why is the class title not suitable as a key field?
It is possible to have more than one class with the same name (e.g. a database design class could run on Monday for one group and on Tuesday for another group)
What is one key differentiator of DB2 on the Cloud?
Its database partitioning feature
the filter KIDS >=1 OR KIDS=0 will
List everyone
Types of information models:
Network model Hierarchical model Relational model Entity-Relationship model Semantic Object-oriented Object-relational model Semi-structured
Entity Integrity Constraint
No primary key value can be NULL
A publishing company produces academic books on various subjects. Books are written by authors who specialise in one or more particular subject. The company employs a number of editors who do not have particular specialisations but who take sole responsibilty for for editing one or more publications. A publication covers a single subject area but may be written by one or more author - the contribution of each author is recorded as a percentage for the purposes of calculating royalties. Indicate the relation which has an incorrect cardinality shown:
None of the Above
A publishing company produces academic books on various subjects. Books are written by authors who specialise in one or more particular subject. The company employs a number of editors who do not have particular specialisations but who take sole responsibilty for for editing one or more publications. A publication covers a single subject area but may be written by one or more author - the contribution of each author is recorded as a percentage for the purposes of calculating royalties. The specification is to be changed so that an author can develop a publication covering more than one subject area and that the schema must be able to store the percentage of the components concerned with each of the subjects. Select an appropriate change to the ER diagram: publication-subject becomes many to many author-subject becomes many to many author-publication becomes many to many more than one of the above none of the above
None of the above
What type of join is needed when you wish to include rows that do not have matching values?
Outer-join
In the evolution of database management systems, what does optimization refer to?
Performance
Which view of information deals with how the information is physically arranged, stored, and accessed?
Physical View
Database
Place to store, retrieve and maintain data efficiently
What is a field that uniquely describes each record?
Primary Key
Relational Calculus
Provides a notation for formulating the definition of that desired relation in terms of those given relations - Tuple-oriented relational calculus - Domain-oriented relational calculus
What do integrity constraint rules help you ensure?
Quality of the information
Which term describes each two-dimensional table or file in the relational model?
Relation
Unique Constraint
Restriction placed on a column to ensure that no duplicate values exist for that column
Consider the following relation: Repayment(borrower_id,name,address,loanamount,requestdate,repayment_date,repayment_amount) Which of the following SQL statements will return all the tuples with information on repayments from borrower_id equal to 42, and where the lent amount exceeds 1000 USD.
SELECT * FROM Repayment WHERE borrower_id=42 AND loanamount>1000;
Inner join syntax example
SELECT * FROM student INNER JOIN enrollment ON student.enrollment_no = enrollment.enrollment_no
Union example syntax
SELECT * FROM student_table_a UNION SELECT * FROM student_table_b
Which ones of the following queries produce exactly 1 result row?
SELECT COUNT(*) FROM PERSONS WHERE PNO > 100
Given a relation country(name, continent, population) which of the following is a valid SQL statement?
SELECT continent, SUM(population) FROM country GROUP BY continent
Consider the following database: MOVIE(id,title,yr) ACTOR(id,name) CASTING(movieid,actorid) Identify the SQL command which will return the titles of all 1959 Marilyn Monroe films.
SELECT title FROM movie,casting,actor WHERE movieid = movie.id AND actor.id = actorid AND name = 'Marilyn Monroe' AND yr = 1959 ;
What does a view allow you to do?
See the contents of a database table Incorrect, Make changes to a database table, Sort a database table
Which of the following statements will list all students born between 1953 and 1959
Select * from studrec where studrec.DOB >= 01/01/1953 AND studrec.DOB <= 31/12/1959
A user is setting up a join operation between tables EMP and DEPT. There are some employees in the EMP table that the user wants returned by the query, but the employees are not assigned to departments yet. Which SELECT statement is most appropriate for this user?
Select e.empid, d.head from emp e right outer join on dept d where e.dept# = d.dept#;
Difference
Set of all tuple belonging to one relation and not the other
Intersection
Set of all tuples belonging to ONLY both relations
Union
Set of all tuples belonging to either relation or both
Domain
Set of atomic values that are all the same type
SQL stands for
Structured Query Language
Which of the following is one of the basic approaches for joining tables?
Subqueries, Union Join, Natural join
STUDENTS entity on left - joined via line with single crows foot symbol on the right to a CARS entity This graphic indicates:
That a student can own 0, 1 or many cars
What SQL structure is used to limit column values of a table?
The CHECK constraint
Primary Key
The best candidate key of a table that is used to uniquely identify records that are stored in a table.
Cartesian Product
The combination of all rows in the first table and all rows in the second table
The number of attributes in a relation is known as:
The relation degree
An athletics meeting involves several competitors who participate in a number of events. The database is intended to record who is to take part in which event and to record the outcome of each event. As results become available the winner attribute will be updated with the cid of the appropriate competitor.
There is no means to represent a tie in a particular event.
A database management system (DBMS) allows you to specify the logical organization for a database and access and use the information within a database.
True
A formal description of all the database relations and all of the relationships existing between them is called a database schema.
True
An information model is an abstract, formal representation of entities that includes their properties, relationships and the operations that can be performed on them.
True
ODBC requires manual installation of the ODBC driver manager and driver on all client machines. JDBC drivers are written in java and JDBC code is automatically installable, secure, and portable on all platforms.
True
T/F - >=1 is the same as >0 (for integers)
True
T/F - A foreign key can accept a null value unless otherwise restricted by a null constraint.
True
T/F - If a salesperson attempts to order merchandise for a customer not in the customer service database, the database will typically generate an error message. This message indicates that an integrity constraint has been violated.
True
The JDBC API is a Java API that can access any kind of tabular data, especially data stored in a relational database.
True
The result of a SELECT statement can contain duplicate rows.
True
The size of SQL data types differ across platforms.
True
UPDATE syntax example
UPDATE myTable SET col1 = -1 WHERE col2 < 0
Full Outer Join
Union of the results of an equi-join including matching rows from the LEFT and RIGHT table
Left Outer Join
Union of the results of an equi-join including matching rows from the LEFT table
Right Outer Join
Union of the results of an equi-join including matching rows from the RIGHT table
To increase the number of nullable columns for a table,
Use the alter table statement.
In the world of SQL relational database, the data type for varchar, in general, is:
Variable length string up to n characters
Insertion Anomalies
When you can't insert a record without also adding unrelated data to the record
An entity is
a "thing" which has either a real physical existence (e.g. a car or a student) or a conceptual existence (e.g. a course)
An atomic field is
a single valued field
In JDBC, what represents a single instance of a particular database session?
a thread
Check Constraint
allows you to limit the types of data a user can insert into a the database and that they meet a certain condition.
It is impossible to represent which of the following in a relational schema?
any mandatory participation constraint in a many-to-many relationship
It is impossible to represent which of the following in a relational schema? any mandatory participation constraint in a many-to-one relationship any mandatory participation constraint in a many-to-many relationship a one-to-one relationship a many-to-one relationship a ternary relationship
any mandatory participation constraint in a many-to-many relationship
The ER model is meant to
be close to a users perception of the data
The AND logical operator is represented
by the overlap of two sets (the intersection)
Relation Instance
current set of rows for a relation schema
The rule that specifies that there should be no repeating fields and that fields should be atomic is
first normal form
COURSES entity to STUDENTS entity via a double crows foot link
indicates that there is a missing entity because this is an invalid relationship
INSERT syntax example
insert into myTable values (1);
the filter KIDS=1 OR RES=True will
list only those who have one child as well as all those who are resident
The SQL statement "delete from R;"
may also remove tuples in tables other than R.
Which of the following is NOT a valid relationship for an entity relationship model?
n-to-n
Atomic
non-decomposable
A publishing company produces academic books on various subjects. Books are written by authors who specialise in one or more particular subject. The company employs a number of editors who do not have particular specialisations but who take sole responsibilty for for editing one or more publications. A publication covers a single subject area but may be written by one or more author - the contribution of each author is recorded as a percentage for the purposes of calculating royalties. The specification is to be changed so that an author can develop a publication covering more than one subject area and that the schema must be able to store the percentage of the compents concerned with each of the subjects. Select an appropriate change to the ER diagram:
none of the above
crowsfoot graphic: the crows foot is on the right. This symbol represents a:
one to many relation
Consider the table (Relation) STUDREC, The key is SREF (student reference number). This table is in
second normal form
The rule that requires that each non-key field (attribute) should be fully functionally dependent on the primary key is
second normal form
Difference example syntax
select * from student_table_a EXCEPT select * from student_table_b
Intersection example syntax
select * from student_table_a INTERSECT select * from student_table_b
Referential Integrity Constraint
statement that limits the values of the foreign key to those already existing as primary key values in the corresponding relation
If a piece of data is stored in two places in the database, then
storage space is wasted and changing the data in one spot will cause data inconsistency
Consider the table (Relation) CARS, The key is REGNO (car registration number), OID is a foreign key that identifies the car's owner.
third normal form
Most RDB designers will accept that in order to deal with the complexities of "real life" transactions, a design needs to reach
third normal form
The rule that prohibits transitive dependencies is
third normal form
