Database 1

Pataasin ang iyong marka sa homework at exams ngayon gamit ang Quizwiz!

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


Kaugnay na mga set ng pag-aaral

ECON 401 Midterm (Chapters 4, 5, 6 & 7)

View Set

Med Surge (Lewis) Exam 1 - Evolve Q&A

View Set

Marketing Chapter 8, 9, 10, 11 test 3

View Set

Chapter 2 - Symmetric Encryption and Message Confidentiality

View Set

1B Detecting and Evaluating Coagulation Inhibitors and Factor Deficiencies

View Set

Computers101 - Internet and Windows Q2

View Set