Jay Study Database

Identifying attribute. Underlying (hash line though instead of solid line)

For each weak entity, we need to provide a weak _________ _________. How do you notate it within an ERD?

d) Requirements collection and analysis

During which step of the database design process are user needs and system requirements identified and documented? a) Conceptual design b) Database implementation c) Physical design d) Requirements collection and analysis

CREATE TABLE faculty ( id INTEGER, rank VARCHAR2(50) NOT NULL, room VARCHAR2(20) NOT NULL, istenured VARCHAR2(1), departmentid Integer, PRIMARY KEY (id), FOREIGN KEY ( id ) REFERENCES users ( id ), FOREIGN KEY (departmentid ) REFERENCES departments ( id ), CONSTRAINT fa_te CHECK (istenured = 'Y' OR istenured = 'N'));

Show me an example of SQL syntax that would put a constraint on a column on what it allows.

b) Hierarchical model

What was the primary data model used by the first database systems introduced in the mid-sixties? a) Relational model b) Hierarchical model c) Object-oriented model d) Network model

b) When entities have optional relationships.

When should "One-to-Many Relationships" be represented in a logical database design? a) When all relationships should be one-to-many. b) When entities have optional relationships. c) When entities have strong relationships. d) When entities have composite attributes.

Numerical

Which is preferred between numerical and text key?

Synthetic

Which is preferred between synthetic vs. natural primary keys?

c) Relation (Table)

What is the primary data structure defined by the relational model? a) Tuple b) Attribute c) Relation (Table) d) Database

c) In columns and rows

According to the relational model, how is data organized within a relation (table)? a) In a three-dimensional structure b) In a hierarchical structure c) In columns and rows d) In a network structure

c) Each cell contains a single, indivisible value.

According to the relational model, what does it mean for the value of each cell in a relation/table to be "atomic"? a) Each cell contains a unique identifier. b) Each cell contains multiple values. c) Each cell contains a single, indivisible value. d) Each cell contains a computed value.

e. The attribute "B" is functionally dependent on the attribute "A"

For the functional dependency A->B, which of the following statement(s) is TRUE? Note: multiple answers are possible a. The attribute "B" functionally determines on the attribute "A" b. The attribute "A" is functionally dependent on the attribute "B" c. The attribute "B" is functionally dependent on the attribute "A" and attribute "A" is functionally dependent on the attribute "B" d. One can find the value of the attribute "A" when they know the value of attribute "B" e. The attribute "B" is functionally dependent on the attribute "A"

CREATE TABLE departments ( id INTEGER, name VARCHAR2(50) NOT NULL, phone VARCHAR2(20) NOT NULL, address VARCHAR2(120), PRIMARY KEY (id) );

Give me a sample SQL syntax of how to create a table using with numbers, text, NOT NULL values, and primary key.

b) The entity at the highest level or root of the hierarchy.

In a parent-child hierarchy, what does the "parent" entity typically represent? a) The most specialized entity in the hierarchy. b) The entity at the highest level or root of the hierarchy. c) The entity with the fewest attributes. d) The entity with the most derived attributes.

b) By using pointers

How are one-to-many relationships defined in the network model? a) By using tables b) By using pointers c) By creating circular relationships d) By using primary keys

c) By using keys to match up rows of data

How are relationships between tables represented in the relational model? a) Using hierarchical structures b) By explicitly defining relationships c) By using keys to match up rows of data d) By eliminating relationships altogether

c) By establishing parent-child relationships using foreign keys.

How can a hierarchical relationship between entities be represented in a database schema? a) By using one-to-one relationships between all entities. b) By including a "Hierarchy Level" attribute in each entity. c) By establishing parent-child relationships using foreign keys. d) By using multi-value attributes.

c) By including attributes that indicate parent-child relationships.

How can hierarchies be represented in a logical database design? a) By eliminating all hierarchical structures. b) By using derived attributes to represent hierarchy. c) By including attributes that indicate parent-child relationships. d) By establishing one-to-one relationships between all entities.

Create a circle with an O in it. Connect it between the superclass and subclass.

How do you notate a "Is-A-Hierarchy" within an ERD?

Underlining

How do you notate a key within an ERD?

Double lines

How do you notate a weak entity within an ERD?

Double lines between the entities. Put it on the side of the relationship (diamond) where the constraint is.

How do you notate constraints within an ERD?

b) By implementing relationships with pointers

How does the network model address performance issues when navigating through records? a) By using circular relationships b) By implementing relationships with pointers c) By organizing data in a flat structure d) By eliminating relationships

b) By using records and sets

How does the network model extend the hierarchical model in organizing data relationships? a) By introducing a tabular structure b) By using records and sets c) By eliminating one-to-many relationships d) By allowing circular relationships

a) In a single large table of facts

How is data organized in a dimensional model database? a) In a single large table of facts b) In multiple small tables with complex relationships c) In a networked structure d) In a hierarchical structure

c) It is the candidate key chosen to be the unique identifier.

How is the primary key of a relation determined? a) It consists of all attributes in the relation. b) It is any combination of attributes chosen by the database administrator. c) It is the candidate key chosen to be the unique identifier. d) It is always the first attribute in the relation.

d. The primary key for "Policy_Dep" is the combination of 'pname' and 'ssn'

If you decide to merge Policy relationship with Dependents table, and create an entity called Policy_Dep for these two entities, which of the following is TRUE for your entities in the logical design? a. Since "Employee" is a weak entity, "pname" will be an attribute in the "Employees" table b. There is no way that you can merge the "Policy" and "Dependents" entities c. All other statements are false d. The primary key for "Policy_Dep" is the combination of 'pname' and 'ssn' e. Since "Dependents" is a weak entity, "ssn" cannot be an attribute in the "Policy_Dep" relation

d. The "IS_A" hierarchy can be represented using a 1:N relationship

In a "IS A hierarchy", which of the following is FALSE? a. All attributes in the superclass are inherited by all subclasses b. There is no unique mapping between the is_a hierarchy and the corresponding relational schema c. The subclasses should not have a key represented d. The "IS_A" hierarchy can be represented using a 1:N relationship

a) In a single, two-dimensional structure

In a flat file model, how is data organization typically represented? a) In a single, two-dimensional structure b) In a hierarchical tree structure c) In a networked database structure d) In multiple interconnected tables

c) A key that uniquely identifies each tuple in the table

In a relation, what is the primary key? a) The key with the highest value in the table b) The key used for indexing purposes c) A key that uniquely identifies each tuple in the table d) A key that is not unique but is commonly used for searching

c) Values in a column must all have the same type.

In a relation/table, what is the requirement regarding the data type of values within a given column/attribute? a) Values in a column can have different types. b) Values in a column must have different types. c) Values in a column must all have the same type. d) Values in a column must be sorted.

c) In a single, two-dimensional structure

In a relational database, how are data organized compared to the flat file model? a) In hierarchical structures b) In separate, unrelated tables c) In a single, two-dimensional structure d) In circular relationships

b) It represents a unique identifier for the entity.

In an ER diagram, what is the purpose of a primary key in an entity? a) It helps define the entity's attributes. b) It represents a unique identifier for the entity. c) It specifies the entity's relationships. d) It indicates the entity's subtype.

b. Any minimal set of attributes for which the closure is the set of all attributes in the relation

In terms of functional dependencies, which of the following is the definition of a candidate key? a. Any set of attributes for which the closure is the set of all attributes in the relation b. Any minimal set of attributes for which the closure is the set of all attributes in the relation c. Any attribute that appears on both sides of a functional dependency d. The combination of all attributes in a table e. Any attribute that appears on a left side of a functional dependency

c) A dependent can have more than one employee purchase an insurance policy

In the ER Diagram shown above, we want to represent the fact that an employee of a company can have medical insurance dependents. Assuming that all the errors were fixed as per question 1, which of the following are FALSE? a) pname is a weak key b) The combination of "ssn", "name", and "lot" are a super key in the Employee table c) A dependent can have more than one employee purchase an insurance policy d) An employee can have more than one dependent e) The "since" attribute is a descriptive attribute

a) 1 on the Employee side and N on the dependent side

In the ER Diagram shown above, we want to represent the fact that an employee of a company can have medical insurance dependents. What would be correct about the cardinality? a) 1 on the Employee side and N on the dependent side b) Cardinality does not applied in this situation c) N On the Employee side, and N on the Dependent side d) N On the Employee side and 1 on the Dependent side e) 1 On the Employee side and 1 on the Dependent side

The errors are following: Participation: There is no dependent (weak entity) who is not in relationship with any employee (strong entity). So, this should be shown with total participation in the diagram. However, the diagram in question doesn't show the total participation symbol. Cardinality: One employee can have many dependents but many dependents can't have a single employee they are depend on. Therefore, this is a one-to-many relationship. This cardinality is also not shown in the diagram given in question.

In the ER diagram shown above, we want to represent the fact that an employee of a company can have medical insurance dependents. Please enumerate the errors you find.

a) The determinant must be a superkey.

In the context of Boyce-Codd Normal Form (BCNF), what must hold true for every non-trivial functional dependency in a relation? a) The determinant must be a superkey. b) The determinant must be a composite key. c) The dependent attribute must be unique. d) The dependent attribute must be a candidate key.

b) Characteristics or properties of entities

In the context of Entity-Relationship diagrams, what are attributes? a) Descriptions of the relationships between entities b) Characteristics or properties of entities c) Constraints that apply to relationships d) The type of database to be used

a) All entities must participate in the relationship.

In the context of database design, what does "Total Participation" in a relationship mean? a) All entities must participate in the relationship. b) Only some entities participate in the relationship. c) Participation is optional for all entities. d) Participation depends on derived attributes.

a) Relationships between three or more entities.

In the context of database modeling, what do "N-ary Relationships" refer to? a) Relationships between three or more entities. b) One-to-one relationships between entities. c) The use of numeric attributes in relationships. d) Hierarchical relationships.

b) A structure where entities are organized in a tree-like fashion with parent-child relationships.

In the context of database modeling, what is a hierarchy? a) A flat database structure with no relationships. b) A structure where entities are organized in a tree-like fashion with parent-child relationships. c) A structure where entities have one-to-many relationships. d) A structure with only one entity.

d) To establish rules that govern how entities and relationships behave

In the database design process using Entity-Relationship diagrams, what is the significance of identifying constraints or business rules? a) To determine the order of attributes b) To specify the data types for attributes c) To define the allowed values for attributes d) To establish rules that govern how entities and relationships behave

c) Measures and dimensions

In the dimensional model, what is the main component that describes the data? a) Tables of facts b) Hierarchical structures c) Measures and dimensions d) Primary keys

a) Data attributes

In the network model, what do records contain? a) Data attributes b) Sets of related records c) Key values d) Hierarchical structures

a) Predicate logic and set theory

In the relational model, what kind of logic and theory are fundamental to its design? a) Predicate logic and set theory b) Hierarchical logic c) Graph theory d) Circular logic

a) Attributes that are calculated or derived from other attributes.

What are "Derived Attributes" in a logical database design? a) Attributes that are calculated or derived from other attributes. b) Attributes that can be part of composite keys. c) Attributes that represent multi-value data. d) Attributes that are unique identifiers.

a) All attributes that are not part of any candidate key.

What are secondary keys in a relational database? a) All attributes that are not part of any candidate key. b) Any combination of attributes that uniquely identify a tuple. c) Attributes that are part of the primary key. d) Keys used for indexing primary keys.

b) Records and sets

What are the two key concepts used in the network model to organize data? a) Tables and columns b) Records and sets c) Hierarchies and trees d) Primary keys and foreign keys

c) They are related to the same real-world entity

What assumption is made about the relationship between data in the same row/line of a flat file model? a) They are unrelated to each other b) They have different meanings c) They are related to the same real-world entity d) They are organized in a multi-dimensional structure

a) Attributes that can have multiple values for each entity.

What do "Multi-Value Attributes" represent in a logical database design? a) Attributes that can have multiple values for each entity. b) Attributes that are shared among multiple entities. c) Attributes that are derived from other attributes. d) Attributes that are part of composite keys.

d) Optimizing performance and storage considerations

What does the physical design step of the database design process focus on? a) Identifying user requirements b) Creating an abstract data model c) Defining the database schema d) Optimizing performance and storage considerations

d) Rows cannot be identical.

What is a fundamental property of rows/tuples in a relation? a) Rows can be identical. b) Rows must be sorted. c) Rows must have different data types. d) Rows cannot be identical.

a) A collection of one or more columns in one table

What is a key in a database? a) A collection of one or more columns in one table b) A database management system c) A data type used for storing numerical values d) A visualization tool for database structures

c) An attribute that is a member of a candidate key.

What is a prime attribute in the context of candidate keys? a) An attribute that is part of the primary key. b) An attribute that is not part of any candidate key. c) An attribute that is a member of a candidate key. d) An attribute that is used for sorting records.

a) A key that is derived from a primary key

What is a secondary key in a database? a) A key that is derived from a primary key b) A key that is not used for indexing c) A key that is not unique in the table d) A key that is used for sorting data within the table

b) Any combination of attributes that uniquely identify a tuple.

What is a superkey in a relational database? a) A key that consists of all available attributes in a relation. b) Any combination of attributes that uniquely identify a tuple. c) An attribute that is part of the primary key. d) A key used for indexing purposes.

b) A minimal set of attributes that uniquely identifies each tuple in a table

What is a superkey in a relational database? a) A key that uniquely identifies each tuple in a table b) A minimal set of attributes that uniquely identifies each tuple in a table c) Any set of attributes that includes a primary key d) A key that is not unique in a table

A row within a table. For example, in a database table named "Employees", each tuple represents an employee.

What is a tuple?

c) The number of instances an entity can be associated with another entity

What is meant by "cardinalities" in the context of identifying cardinalities of relationships? a) The number of entities in a database b) The constraints applied to entities c) The number of instances an entity can be associated with another entity d) The constraints applied to attributes

Referenced table is the table that holds the primary key that is being referenced. The referencing table is the table that is r

What is the difference between referenced table and referencing table?

A natural primary key is derived from real-world attributes and holds inherent meaning, while a synthetic primary key is system-generated and lacks direct real-world significance.

What is the difference between synthetic key and natural key?

b) Requirements collection and analysis

What is the first step in the database design process? a) Conceptual design b) Requirements collection and analysis c) Database implementation d) Physical design

c) Identify entities

What is the first step in the process of database design using Entity-Relationship diagrams? a) Identify relationships b) Identify attributes of entities c) Identify entities d) Identify the constraints

c) Predicate logic and set theory

What is the foundational basis for the relational model in database systems? a) Hierarchical model b) Flat file model c) Predicate logic and set theory d) Network model

b) All data relationships can be structured in hierarchies

What is the fundamental assumption of the hierarchical model in structuring data relationships? a) Data relationships are represented as tables and rows b) All data relationships can be structured in hierarchies c) Data relationships are stored in a networked format d) Data relationships are inherently complex and interconnected

a) To represent entities with strong relationships.

What is the main reason for using weak entities in an ER diagram? a) To represent entities with strong relationships. b) To simplify the diagram and reduce redundancy. c) To define entities that are independent of others. d) To establish many-to-many relationships.

b) A child can only have one parent

What is the main rule of the hierarchical model when it comes to relationships between data elements? a) A child can have multiple parents b) A child can only have one parent c) Data elements are organized in a circular structure d) Data elements are unrelated to each other

d) It facilitates the organization and retrieval of structured data.

What is the primary advantage of representing hierarchical data in a database? a) It simplifies data modeling. b) It enables efficient storage of multi-value attributes. c) It allows for the representation of complex relationships. d) It facilitates the organization and retrieval of structured data.

c) It uniquely identifies each tuple, and you can't remove any attribute while maintaining uniqueness.

What is the primary characteristic of a candidate key in a relation? a) It consists of all attributes in the relation. b) It is a non-minimal combination of attributes. c) It uniquely identifies each tuple, and you can't remove any attribute while maintaining uniqueness. d) It can be removed without affecting the uniqueness of tuples.

c) All information in a column is assumed to have a similar meaning

What is the primary characteristic of a flat file model in data storage? a) It allows for complex data relationships b) It is designed around a multi-dimensional structure c) All information in a column is assumed to have a similar meaning d) It is based on a graph data structure

b) Entities have a primary key, while weak entities do not.

What is the primary characteristic that distinguishes an entity from a weak entity in an ER diagram? a) Entities have attributes, while weak entities do not. b) Entities have a primary key, while weak entities do not. c) Weak entities have a primary key, while entities do not. d) Entities can have relationships with other entities, while weak entities cannot.

a) Eliminating transitive dependencies

What is the primary focus of achieving Third Normal Form (3NF) in database normalization? a) Eliminating transitive dependencies b) Ensuring every attribute is a candidate key c) Allowing for multi-valued attributes d) Ensuring data is stored in a single table

a) Eliminating all data redundancy

What is the primary goal of achieving First Normal Form (1NF) in database normalization? a) Eliminating all data redundancy b) Ensuring data is stored in multiple tables c) Removing NULL values from the database d) Enforcing primary keys in all tables

d) To match up rows of data in different tables

What is the primary purpose of keys in the relational model? a) To create circular relationships b) To simplify data storage c) To eliminate the need for tables d) To match up rows of data in different tables

c) To facilitate easy summarization of data using OLAP queries

What is the primary purpose of the dimensional model in data warehousing? a) To represent data in a highly normalized structure b) To simplify data storage in the operational database c) To facilitate easy summarization of data using OLAP queries d) To eliminate the need for measures and dimensions

c) To establish connections between entities

What is the purpose of identifying relationships in Entity-Relationship diagram design? a) To define constraints for entities b) To determine the database management system to be used c) To establish connections between entities d) To list the attributes of entities

d) To represent entities that exist independently.

What is the purpose of including strong entities in a logical database design? a) To represent entities with weak relationships. b) To eliminate the need for relationships. c) To ensure that all attributes are derived. d) To represent entities that exist independently.

d) The ordering of rows/tuples is irrelevant.

What is the relevance of the ordering of rows/tuples in a relation according to the relational model? a) Rows must be sorted in ascending order. b) The ordering of rows is always relevant. c) Rows/tuples can be in any order. d) The ordering of rows/tuples is irrelevant.

c) It ensures that each cell in a relation/table contains atomic values.

What is the significance of achieving the First Normal Form (1NF) in database design, as mentioned in the provided information? a) It enforces ordering of rows. b) It allows for multi-valued attributes. c) It ensures that each cell in a relation/table contains atomic values. d) It requires rows to be identical.

c) To represent attributes that are composed of sub-attributes.

What is the significance of including composite attributes in a logical database design? a) To simplify the database structure. b) To represent attributes that cannot be derived. c) To represent attributes that are composed of sub-attributes. d) To establish many-to-many relationships.

b) Measures

What is the term used in the dimensional model to represent the numerical values being analyzed? a) Dimensions b) Measures c) Facts d) Attributes

b) OLAP (Online Analytical Processing) queries

What type of queries are typically used with the dimensional model? a) SQL queries b) OLAP (Online Analytical Processing) queries c) Data cleansing queries d) Hierarchical queries

a) Relational databases have powerful and standardized data retrieval methods b) relational database provides data independence

Which of the following are advantages of relational databases? Note: multiple answers are possible a) Relational databases have powerful and standardized data retrieval methods b) relational database provides data independence c) Attributes can be shared by multiple tables d) Relations are related to other in a hierarchical form

a) Declarative c) Precise and clear e) Consistent f) Business-oriented

Which of the following are characteristics of data requirements? a) Declarative b) Complex and technical c) Precise and clear d) Redundant and duplicated e) Consistent f) Business-oriented

b) Data is organized in a tree-like structure with one-to-many relationships

Which of the following best describes the hierarchical model's approach to organizing data? a) Data is organized in a flat, tabular structure b) Data is organized in a tree-like structure with one-to-many relationships c) Data is organized in a network of interconnected nodes d) Data is organized in a matrix-like structure

d) It is typically used for simple, straightforward data storage

Which of the following is a characteristic of a flat file model? a) It is highly normalized b) It is designed for complex querying c) It is suitable for representing structured data d) It is typically used for simple, straightforward data storage

b) It cannot exist without being associated with another entity.

Which of the following is a characteristic of a weak entity in an ER diagram? a) It always has a strong relationship with other entities. b) It cannot exist without being associated with another entity. c) It has its own independent primary key. d) It has no attributes.

a) An attribute that is part of the primary key

Which of the following is a prime attribute in a relation? a) An attribute that is part of the candidate key b) An attribute that is not part of any key in the relation c) An attribute that is used for sorting data within the table d) An attribute that is derived from other attributes in the relation

c) Employee ID in an employee database

Which of the following is an example of a candidate key? a) Social Security Number in an employee database b) Employee Name in an employee database c) Employee ID in an employee database d) Employee Salary in an employee database

a) A list of employees with their respective projects.

Which of the following is an example of a hierarchical structure that can be represented in a database? a) A list of employees with their respective projects. b) A flat list of customer orders. c) An entity with multi-value attributes. d) A list of unrelated entities.

b) Entities should have composite attributes.

Which of the following is one of the rules for converting a conceptual design into a logical design in database modeling? a) Tables should be denormalized to reduce redundancy. b) Entities should have composite attributes. c) Hierarchies should be eliminated. d) Derived attributes should be included.

a. A candidate key is a set of attributes for which, if we remove any attribute, it will lose the key property

Which of the following is true? a. A candidate key is a set of attributes for which, if we remove any attribute, it will lose the key property b. A super key is the most important key in an entity/table c. If we add an extra attribute to a super key, it will become a candidate key d. A primary key is a minimal candidate key

c) Boyce-Codd Normal Form (BCNF)

Which of the following normal forms ensures that no non-prime attribute is transitively dependent on any superkey? a) First Normal Form (1NF) b) Second Normal Form (2NF) c) Boyce-Codd Normal Form (BCNF) d) Third Normal Form (3NF)

b) It eliminates partial dependencies in a relation.

Which of the following statements is true regarding Second Normal Form (2NF)? a) All attributes are fully functionally dependent on the primary key. b) It eliminates partial dependencies in a relation. c) It is also known as Boyce-Codd Normal Form (BCNF). d) It allows for NULL values in attribute fields.

b) The ordering of columns is irrelevant.

Which of the following statements is true regarding the ordering of columns in a relation? a) The ordering of columns is always relevant. b) The ordering of columns is irrelevant. c) Columns must be sorted in ascending order. d) The ordering of columns depends on the database size.

a. Candidate key d. Super Key

Which one of the following is a set of one or more attributes taken collectively to uniquely identify a record? Note: multiple answers are possible. a. Candidate key b. Foreign Key c. Extra Key d. Super Key e. Preferred Key

b) Total Participation

Which rule addresses the representation of entities with relationships that are optional on one side and mandatory on the other side? a) Multi-Value Attributes b) Total Participation c) Composite Attributes d) Hierarchies

c) One-to-One Relationships

Which rule addresses the representation of relationships where each entity is associated with only one other entity? a) One-to-Many Relationships b) Many-to-Many Relationships c) One-to-One Relationships d) Total Participation

a) Conceptual design

Which step of the database design process involves creating an abstract representation of the data model without considering implementation details? a) Conceptual design b) Requirements collection and analysis c) Database implementation d) Physical design