AI Gen Flashcards
What is the primary difference between data and information?
Data are raw facts; Information is the result of processing raw data to reveal its meaning.
What constitutes the building blocks of information?
Data.
Define a database.
An integrated collection of files built for a specific purpose to store corporate data.
How can databases be classified based on the number of users?
- Single-user database
- Multiuser database
- Workgroup database (<50 users)
- Enterprise database (>50 users)
What are the three levels of database architecture?
- External: users’ views of the database
- Conceptual: logical structure of an entire database
- Internal: details of the physical storage
What is the purpose of data modeling?
- Represent the real world
- Helps with efficient computerisation
- Focus on the use of elements
- Represent necessary elements and their relationships
- Reduce data redundancy
What are the four main concepts represented in an ER model?
- Entity
- Attributes
- Relation
- Cardinality
What does an ER diagram (ERD) help designers to do?
Design a database.
Understand and specify the desired components of the database and the relationships among those components.
What shape represents an entity in an ERD?
Rectangle.
What shape represents attributes in an ERD?
Ellipse or oval.
What shape represents relationships among entity sets in an ERD?
Diamond.
What is an attribute in the context of a database entity?
Attributes are used for describing the entities and must include an identifier that is unique for each entity.
How can redundancy in attributes be reduced?
Avoid structured attributes and decompose them into simpler elements.
What is a relation in a database?
A relation specifies the association between two or more entities.
What does maximum cardinality indicate in a relation?
The maximum number of instances on either side of a relation.
What is the Enhanced or Extended Entity Relationship (EER) model?
A conceptual data model that incorporates extensions to the ER model to design more accurate database schemas.
What is specialization in the context of subclasses?
The process of defining a set of subclasses of an entity type based on distinguishing characteristics.
What is generalization in database modeling?
The process of defining a generalized entity type from given entity types.
What is the difference between single inheritance and multiple inheritance?
- Single inheritance: Subclass inherits all attributes from one superclass
- Multiple inheritance: Subclass inherits from more than one superclass.
Fill in the blank: A database is the core _______ technology to store corporate data.
[information systems]
True or False: An entity in a database can exist independently.
True.
What are the types of end users interacting with a database?
- Casual end users
- Naive or parametric end users
- Sophisticated end users
- Standalone users
What does the term ‘cardinality’ refer to in database relations?
The number of associated instances in a relationship.
What is functional dependency in database attributes?
A relationship where one attribute’s value uniquely determines another attribute’s value.
What is the structure of the specialization hierarchy?
A tree structure or strict hierarchy
Every subclass participates as a subclass in only one class/subclass relationship.
What is a specialization lattice?
Subclass can be a subclass in more than one class/subclass relationship
Define single inheritance.
Subclass entity inherits all attributes and relationships of superclass
What is multiple inheritance?
Subclass with more than one superclass
What happens if an attribute from the same superclass is inherited more than once in multiple inheritance?
Included only once in shared subclass
What are the types of specialization constraints?
Overlapping or disjoint
Place ‘o’ or ‘d’ in specialization circle to indicate constraint.
What indicates total specialization?
Double line connecting superclass to specialization circle
What indicates partial specialization?
Single line connecting superclass to specialization circle
What are the three definitions of specialization?
- Predicate-defined
- Attribute-defined
- User-defined
What is a category or union type?
Represents a single superclass/subclass relationship with more than one superclass
In a category type, how is attribute inheritance handled?
Works more selectively
What is the cardinality between supertype and subtype?
Always 1:0
What design choices can be made for specialization and generalization?
- Define many specializations and subclasses
- Merge subclass into superclass if few specific attributes
- Replace with type attributes
What should generally be avoided in design choices?
Union types and categories
What is the definition of a record in data storage?
Describes entities and their attributes
What constitutes a record type?
A collection of field names and their corresponding data types
What are the two types of record lengths?
Fixed or variable lengths
What is a file in data organization?
A sequence of records
When is a file said to have fixed-length records?
If every record in the file has exactly the same size in bytes
What is meant by variable-length records?
Records may have different sizes due to varying field sizes or optional fields
What is a spanned organization in file structure?
Records can span more than one block
What is an unspanned organization?
Records are not allowed to cross block boundaries
What are the two types of operations on files?
- Retrieval
- Update
What does the open operation do in file management?
Prepares the file for reading or writing
What is the purpose of the find operation?
Searches for the first record that satisfies a search condition
What is a heap (pile) file?
Records are placed in the file in the order they are inserted
What is a relative or direct file?
Unordered fixed-length records using unspanned blocks and contiguous allocation
How are sorted files organized?
Physically order the records based on the values of one of their fields called the ordering field
What is hashing in file organization?
Provides very fast access to records based on certain search conditions
What is a B-Tree?
Allows an indexing field value to appear only once at some level in the tree
What is a primary index?
Ordered index with one entry record for each block
What is a clustering index?
Records physically ordered by a non-key field
What is a dense secondary index?
Includes several index entries with the same value of the indexing field
What is a static multilevel index?
Considers the index file as an ordered file with distinct values of the indexing field
What is a static multilevel index?
An index where the first level is an ordered file with distinct values of the indexing field, requiring updates when the data file is modified.
What is the formula for determining the number of levels in a multilevel index?
t = ┌ logfo r1┐ where r1 is the first-level entries and fo is the fanout.
What is an indexed sequential file?
A file organization where the data file is ordered with a multilevel primary index on its ordering key field.
What are the benefits of dynamic multilevel indexes?
They retain multilevel indexing benefits while reducing insertion and deletion problems by automatically reorganizing with local changes.
What is a B-tree?
A type of dynamic multilevel index that allows efficient insertion and deletion operations.
What is a hash index?
An index that organizes indexing fields and their associated pointers into a hash file structure.
What is a database management system (DBMS)?
A software system that manages large, shared, and persistent collections of data, ensuring reliability and privacy.
What is SQL?
Structured Query Language, a standardized programming language used for managing and communicating with relational databases.
What are schemas in a database?
The design of a database, which can be at physical or logical levels, and is made up of the characteristics of the data.
What is the role of a database administrator (DBA)?
Responsible for the design, control, and administration of the database.
What is the conceptual model to physical model transformation?
An entity becomes a table, an instance becomes a row, and an attribute becomes a column.
What does DDL stand for and what is its purpose?
Data Definition Language; used for specifying the database schema and defining logical, external, and physical schemas.
What is DML used for?
Data Manipulation Language; used for accessing and manipulating data in a database.
What is the purpose of DCL?
Data Control Language; used for granting and revoking user access on a database.
What are the basic SQL commands?
- Select
- Insert
- Update
- Delete
- Create
- Drop
What do angular brackets < and > denote in SQL syntax?
Terms that must be enclosed.
What are the elementary domains in SQL?
- Characters
- Bit
- Exact numeric domains
- Approximate numeric domains
- Date and time
- Temporal intervals
What is the basic syntax for creating a table in SQL?
CREATE TABLE table_name(column1 datatype, column2 datatype, …, PRIMARY KEY(column));
How do you insert a new row into a table in SQL?
INSERT INTO TABLE_NAME [(column1, column2, …)] VALUES (value1, value2, …);
What does the SELECT clause do in an SQL query?
Lists the attributes desired in the result of a query.
What is the purpose of the WHERE clause in SQL?
Defines the selection predicate involving attributes of the relations in the FROM clause.
What does the UPDATE query do in SQL?
Modifies existing records in a table.
What is the syntax for deleting records in SQL?
DELETE FROM table_name WHERE [condition];
What does the IN operator do in SQL?
Compares a value to a list of literal values.
True or False: The LIKE operator is used to compare a value to similar values using wildcard operators.
True
What is the purpose of the CONCAT function in SQL?
Concatenates two or more strings.
What does the ROUND function do in SQL?
Rounds a number to the nearest integer or specified decimal places.
What is the significance of the sysdate in SQL?
Retrieves the current date and time.
What does the LENGTH function do in SQL?
Returns the length of a string.
What does the TRUNC function do in SQL?
Truncates a date to a specified unit (e.g., year, month).
What does SQL stand for?
Structured Query Language
What is the purpose of a query optimizer in a DBMS?
Analyzes SQL queries and determines efficient execution mechanisms
What does QBE stand for?
Query By Example
What is Query By Example (QBE)?
A graphical way of querying relational data using example tables
True or False: QBE provides a user-friendly method for running database queries.
True
Fill in the blank: In SQL, the command to insert a new employee record is _______.
INSERT INTO
What SQL command is used to update records in a table?
UPDATE
What condition is used to filter results in SQL queries?
WHERE
List the types of aggregation functions in SQL.
- AVG
- COUNT
- MAX
- MIN
- SUM
What is the purpose of the ‘CASE’ statement in SQL?
To provide conditional logic in queries
What is the SQL command to delete records from a table?
DELETE
Fill in the blank: The command used to retrieve data from a database is _______.
SELECT
What does ‘LOWER’ function do in SQL?
Converts a string to lowercase
What does ‘COALESCE’ function do in SQL?
Returns the first non-null value in a list
Fill in the blank: To concatenate strings in SQL, use the _______ operator.
||
What is the command to modify the structure of a table?
ALTER TABLE
What is ‘Embedded SQL’?
SQL statements contained within an application programming language
What is the purpose of the ‘GROUP BY’ clause in SQL?
To group rows that have the same values in specified columns
What SQL function would you use to count distinct values in a column?
COUNT(DISTINCT column_name)
What is the typical use of non-procedural queries?
Data retrieval without needing to know physical data storage format
What does the ‘HAVING’ clause do in SQL?
Filters groups based on a specified condition
List the components needed for Embedded SQL in VB.Net.
- MySQL workbench
- MySQL connector
- MySQL for Visual Studio
- Visual Studio
What is the command to add a new column to an existing table?
ALTER TABLE table_name ADD column_name data_type
True or False: QBE allows for complex query conditions to be expressed.
True
What is the purpose of the ‘ORDER BY’ clause in SQL?
To sort the result set of a query by one or more columns
Fill in the blank: To display unique values in a query, use the _______ keyword.
DISTINCT
What is the SQL command to combine two or more conditions in a query?
AND / OR
What is the purpose of ‘INSERT INTO’ command in SQL?
To add new records to a table
What does the ‘TRUNC’ function do in SQL?
Rounds a number down to the nearest integer or truncates a date
What is the command to change the data type of a column in SQL?
ALTER TABLE table_name MODIFY column_name new_data_type
