Database Systems Midterm
Exam fully solved & updated
(latest version)
What are the database-specific definitions of: Entity, Attribute, Relationship,
and Metadata?
(SPECIAL) - answer Entity: An object or concept found in the problem domain
which needs to be persisted and later accessed.
Attribute: A property of a persisted entity identified by a unique name and a
data type.
Relationship: An association between two entities maintained by the
database.
Metadata: Data about Data or the data maintained by the DBMS which
describes a database's tables attributes, relationships, etc.
What is the acronym used to describe the four basic operations that can be
performed on a database's data?
Briefly describe each operation in terms of tables, table rows, and row
attributes. - answer CRUD
Create: Create (insert) a new row into a table.
Retrieve: Retrieve one or more rows from a database table.
Update: Update the attribute values associated with one or more rows of a
database table.
Delete: Remove (delete) one or more rows from a database table.
Provide a brief definition of database based on the material in the slides.
Provide a brief definition of DBMS based on the material in the slides.
What is the relationship between DBMS and database? - answer A database
is a persistent collection of entity instances, their individual attributes, and
relationships between the entities.
,The DBMS is a server used by client application to maintain persistent entity
instances. Collections of instances persistently maintained in files on disks.
A DBMS maintains and provides access to one or more databases.
Do your best to create SQL for the following queries. Use Slide 15 as a
template.
Create a freshman student with the attributes name Yu, CS major, and ID 23.
List the grades earned by student with ID 8.
Change the course CS3380 to a four credit hour course.
Remove MATH2410 as a prerequisite of CS3380. - answer INSERT INTO
STUDENT VALUES ('Yu', 23, 1, 'CS');
SELECT * FROM GRADE_REPORT WHERE STUDENT_NUMBER = 8;
UPDATE COURSE SET (CREDIT_HOURS = 4) WHERE COURSE_NUMBER =
'CS3380';
DELETE from PREREQUISITE WHERE COURSE_NUMBER = 'CS3380' AND
PREREQUISITE_NUMBER = 'MATH1310';
What is the meaning / special property of an entity's (table) 'identity
attribute'?
How are relationships between entities maintained in a Relational DBMS? Use
GRADE_REPORT from the University schema as an example.
(SPECIAL) - answer The identity attribute is an entity (table) attribute that
uniquely identifies every entity instance. That is, a unique value is assigned
to every row of the table which reflects the unique identity of every entity
instance stored in the database (every unique Student, Customer, Purchase,
etc. has a unique identity attribute value).
Relationships are maintained between entities (table) by referencing the
identity attribute of one entity by a second entity. The referencing entity
maintains an attribute whose values are the identity of the referenced entity.
For example in the University schema, the entity GRADE_REPORT maintains
two referencing attributes Student_number (STUDENT) and Section_number
(SECTION).
, What are the parallels between concepts from Object-Oriented and from
Relational Databases? - answer In both cases, we are maintaining important
entities, entity instances, attributes of the entities, and relationships
between entities from the application's problem domain. Tables are like OO
classes. Tables maintain columns which are similar to class attributes.
Instances of classes (objects) are like table rows. Classes maintain
associations and tables maintain relationships e.g. the Grade_Report table
maintains references to Student and Section.
Describe the four DBMS user roles described in the slides including the
responsibilities assigned to each role. - answer Database Operators:
Operators are responsible for maintaining the servers (machines) that host
the DBMS and ensuring the DBMS is operating and available to its clients.
Database Administrators: Administrators is responsible for creating and
maintaining the database schema. Also approves changes to existing
schema requested by the application developers.
Application Developers: Responsible for identifying the application entities,
attributes, and relationships that need to be captured / persisted in a
database schema. Works with the DBA to create the schema that meets the
application's needs.
Clients: Client applications that CRUD database information. Applications are
given roles that restrict their DBMS access to specific databases.
Name and briefly describe (1-2 sentences) the First Eight Advantages of
Using the DBMS Approach given in the book (not slides). - answer Controlling
Redundancy: Control the problem maintaining the same entity instance twice
in the datastore i.e. maintaining the same 'person' as two Customers.
Restricting Unauthorized Access: Ensuring that authorized users / clients are
provided the correct access to the information maintained by the database.
Based on the use of authenticated Client IDs and Roles.
Providing Persistent Storage of Application Data: Persisting (making
permanent) application entities (objects) CRUD by clients.
Providing Search Techniques for Efficient Query Processing: The database
maintains data structures, called indexes, that make for the efficient (fast)
location of rows matching some query's parameters from tables with very
large numbers of rows.
Exam fully solved & updated
(latest version)
What are the database-specific definitions of: Entity, Attribute, Relationship,
and Metadata?
(SPECIAL) - answer Entity: An object or concept found in the problem domain
which needs to be persisted and later accessed.
Attribute: A property of a persisted entity identified by a unique name and a
data type.
Relationship: An association between two entities maintained by the
database.
Metadata: Data about Data or the data maintained by the DBMS which
describes a database's tables attributes, relationships, etc.
What is the acronym used to describe the four basic operations that can be
performed on a database's data?
Briefly describe each operation in terms of tables, table rows, and row
attributes. - answer CRUD
Create: Create (insert) a new row into a table.
Retrieve: Retrieve one or more rows from a database table.
Update: Update the attribute values associated with one or more rows of a
database table.
Delete: Remove (delete) one or more rows from a database table.
Provide a brief definition of database based on the material in the slides.
Provide a brief definition of DBMS based on the material in the slides.
What is the relationship between DBMS and database? - answer A database
is a persistent collection of entity instances, their individual attributes, and
relationships between the entities.
,The DBMS is a server used by client application to maintain persistent entity
instances. Collections of instances persistently maintained in files on disks.
A DBMS maintains and provides access to one or more databases.
Do your best to create SQL for the following queries. Use Slide 15 as a
template.
Create a freshman student with the attributes name Yu, CS major, and ID 23.
List the grades earned by student with ID 8.
Change the course CS3380 to a four credit hour course.
Remove MATH2410 as a prerequisite of CS3380. - answer INSERT INTO
STUDENT VALUES ('Yu', 23, 1, 'CS');
SELECT * FROM GRADE_REPORT WHERE STUDENT_NUMBER = 8;
UPDATE COURSE SET (CREDIT_HOURS = 4) WHERE COURSE_NUMBER =
'CS3380';
DELETE from PREREQUISITE WHERE COURSE_NUMBER = 'CS3380' AND
PREREQUISITE_NUMBER = 'MATH1310';
What is the meaning / special property of an entity's (table) 'identity
attribute'?
How are relationships between entities maintained in a Relational DBMS? Use
GRADE_REPORT from the University schema as an example.
(SPECIAL) - answer The identity attribute is an entity (table) attribute that
uniquely identifies every entity instance. That is, a unique value is assigned
to every row of the table which reflects the unique identity of every entity
instance stored in the database (every unique Student, Customer, Purchase,
etc. has a unique identity attribute value).
Relationships are maintained between entities (table) by referencing the
identity attribute of one entity by a second entity. The referencing entity
maintains an attribute whose values are the identity of the referenced entity.
For example in the University schema, the entity GRADE_REPORT maintains
two referencing attributes Student_number (STUDENT) and Section_number
(SECTION).
, What are the parallels between concepts from Object-Oriented and from
Relational Databases? - answer In both cases, we are maintaining important
entities, entity instances, attributes of the entities, and relationships
between entities from the application's problem domain. Tables are like OO
classes. Tables maintain columns which are similar to class attributes.
Instances of classes (objects) are like table rows. Classes maintain
associations and tables maintain relationships e.g. the Grade_Report table
maintains references to Student and Section.
Describe the four DBMS user roles described in the slides including the
responsibilities assigned to each role. - answer Database Operators:
Operators are responsible for maintaining the servers (machines) that host
the DBMS and ensuring the DBMS is operating and available to its clients.
Database Administrators: Administrators is responsible for creating and
maintaining the database schema. Also approves changes to existing
schema requested by the application developers.
Application Developers: Responsible for identifying the application entities,
attributes, and relationships that need to be captured / persisted in a
database schema. Works with the DBA to create the schema that meets the
application's needs.
Clients: Client applications that CRUD database information. Applications are
given roles that restrict their DBMS access to specific databases.
Name and briefly describe (1-2 sentences) the First Eight Advantages of
Using the DBMS Approach given in the book (not slides). - answer Controlling
Redundancy: Control the problem maintaining the same entity instance twice
in the datastore i.e. maintaining the same 'person' as two Customers.
Restricting Unauthorized Access: Ensuring that authorized users / clients are
provided the correct access to the information maintained by the database.
Based on the use of authenticated Client IDs and Roles.
Providing Persistent Storage of Application Data: Persisting (making
permanent) application entities (objects) CRUD by clients.
Providing Search Techniques for Efficient Query Processing: The database
maintains data structures, called indexes, that make for the efficient (fast)
location of rows matching some query's parameters from tables with very
large numbers of rows.
