Mid Term study

Question 1

What is the Range of Database applications?

Answer 1

-Personal Databases

-Multitier Client/Server Databases

-Enterprise Applications(
* Enterprise Resource Planning
* Data Warehousing)

Question 2

What are the THREE development stage models?

Answer 2

Conceptual model

Logical Model

Physical Model

Question 3

Problems with Data Dependency?

Answer 3

Each application programmer must maintain his/her own data

Lack of Coordination and Central Control

Non-standard file formats

Question 4

Costs and Risks of Database Approach?

Answer 4

New, specialized personnel

Installation and management cost and
complexity

Conversion costs

Need for explicit backup and recovery

Organizational conflict

Question 5

What is the Database Approach?

Answer 5

Central repository of shared data

Data is managed by a controlling agent

Stored in a standardized, convenient
form

Requires a Database Management System (DBMS)

Question 6

What are CASE Tools

Answer 6

computer-aided software engineering Tools

Question 7

What is a Repository?

Answer 7

centralized storehouse of metadata

Question 8

What are End Users?

Answer 8

people who use the applications and
databases

Question 9

Define Metadata?

Answer 9

Data about data(information about data)

Provides context to the data.

Example: if ‘Salary’ for an employee is 65000.

65000 is the data and ‘Salary’ is the context or metadata.

Question 10

Define Conceptual Data Modelling

Answer 10

• Define detailed inventory of data attributes
• Lists all the data categories
• Establishes relations between data categories

Results in conceptual schema that is usually
displayed graphically
(known as the Entity-Relationship Diagram)

Question 11

Define Logical Data Modelling

Answer 11

Transform The conceptual schema to logical schema

Describe data in terms of data management
technology that will be used (i.e relational database)

Transform data to atomic elements through the process of normalization (tune the conceptual model)

Question 12

Define Physical Data Modelling

Answer 12

Describe how data will be stored and managed by the technology (i.e RDBMS)

Responsible for specifications for memory
management, storage layout, data indexing, etc

Results in a physical schema

Question 13

Select Query syntax

Answer 13

SELECT [column name] from [table name]
WHERE [Condition]

Question 14

Selecting null or not null syntax

Answer 14

SELECT * FROM [TABLE] WHERE Column name IS NULL
IS NOT NULL

Question 15

BETWEEN and IN clause examples

Answer 15

BETWEEN:
SELECT * FROM [TABLE]
WHERE [Column name] BETWEEN VAL-1 AND VAL-2

IN:
SELECT * FROM [TABLE]
WHERE [Column name] IN (VAL-1,VAL-2, ETC)

Question 16

LIKE Clause: What do these % and _ mean?

Answer 16

//name starts with any 1 char, ends w/ erman
WHERE [Column name] LIKE ‘_erman’

//name starts w/ h with anything after
WHERE [Column name] LIKE ‘h%’

Question 17

Date functions?

Answer 17

SYSDATETIME,
DATENAME(year, ‘2017/08/25’) AS DatePartString;,
DATEPART(year, ‘2017/08/25’) AS DatePartInt;,
DATEDIFF(year, ‘2017/08/25’, ‘2011/08/25’) AS DateDiff;

Question 18

String functions?

Answer 18

CONCAT(‘W3Schools’, ‘.com’);

REPLACE(‘SQL Tutorial’, ‘T’, ‘M’);

RTRIM(‘SQL Tutorial ‘)

UPPER(‘sql’)
LOWER[‘SQL’)

SUBSTRING(‘SQL Tutorial’, 1, 3)
//SQL extracted

LEN(‘SQL’)

Question 19

Mathematical functions

Answer 19

ROUND(235.415, 2)
//round to 2 decimal places

CEILING(25.75)

FLOOR(25.75)

Question 20

Aggregate functions

Answer 20

MIN()
MAX()
COUNT()
AVG()
SUM()
CAST()

Question 21

What are business rules?

Answer 21

Basis for the data model.

Define what data does your business needs and define
how to govern the data.

Question 22

What does the ER diagram consist of?

Answer 22

Entities:
– Entity Type – collection of entities (often corresponds to a table)
* Relationships:
– Relationship type – category of relationship; link between entity types
* Attributes:
– Properties or characteristics of an entity or relationship type (often corresponds to a field in a
table)

Question 23

What are Supertypes and Subtypes?

Answer 23

Supertype is a generic entity type that has one or more Subtype.

Question 24

Explain the Completeness constraints

Answer 24

Total Specialization Rule: An instance of a supertype must also be a subtype

Partial Specialization Rule: An instance of a supertype doesn’t have to be a subtype

Question 25

Explain the Disjointness Constraints

Answer 25

Disjoint rule: A Supertype can only have one subtype

Overlap Rule: A Supertype can have one or more subtypes

Question 26

Two Approaches to Make Relational Models

Answer 26

Generalization and Specialization

Question 27

Generalization is a ______-__ approach where we start looking at ______ and then categorize all entities with similar
attributes to ______ _____

Answer 27

Generalization is a bottom-top approach where we
start looking at entities and then categorize all entities with similar
attributes to entity types

Question 28

Specialization Is a ___-_______ approach where we start thinking of
the more ______ entities (the big picture) and then drill down till
we can get to the ____ __________ entity types. This approach is more
inline with the _________ __________ modeling.

Answer 28

Specialization Is a top bottom approach where we start thinking of
the more abstract entities (the big picture) and then drill down till
we can get to the more concise entity types. This approach is more
inline with the Object Oriented O-O modeling.

Question 29

What does ACID stand for?

Answer 29

ATOMICITY
CONSISTENCY
ISOLATION
DURABILITY

Question 30

Explain the A in ACID

Answer 30

A - Atomicity:
This principle ensures that a transaction is treated as a single unit, which either fully happens or doesn’t happen at all. If any part of the transaction fails, the entire transaction is rolled back, leaving the database in its initial state before the transaction was attempted.

Question 31

Explain the C in ACID

Answer 31

C - Consistency:
Consistency ensures that a transaction can only bring the database from one valid state to another, maintaining all predefined rules, including constraints, cascades, and triggers. The data must be consistent before and after the transaction.

Question 32

Explain the I in ACID

Answer 32

I - Isolation:
Isolation ensures that transactions are securely and independently processed simultaneously without interference, guaranteeing that transactions occurring concurrently result in the same state as if they were processed sequentially. This prevents transactions from reading intermediate data from each other.

Question 33

Explain the D in ACID

Answer 33

D - Durability:
Durability guarantees that once a transaction has been committed, it will remain so, even in the event of a power loss, crash, or error. Committed transactions are recorded in non-volatile memory, ensuring that the effects of the transaction are permanently reflected in the database.

Question 34

What is CAP Theorem

Answer 34

in a distributed database system, it is impossible to simultaneously guarantee Consistency (all nodes see the same data at the same time), Availability (every request receives a response about whether it was successful or failed), and Partition Tolerance (the system continues to operate despite network partitions).

A distributed system can only achieve two of these three things so a trade-off must be made.

Question 35

Scale-up vs Scale-out

Answer 35

Scale-up: Vertical scaling, make one machine stronger by adding more resources.

Scale-out: Horizontal scaling, Add more machines to share the load.

Question 36

Schema on read is?

Answer 36

Schema on read means that you can
create your own structure from
unstructured or loosely structured
data when you read the data.
Example: Relational Databases

Question 37

Schema on write is?

Answer 37

Schema on write requires a well
defined structure (tables in relational
databases) and you need to adhere
to this schema when writing to the
the database
Example: MongoDB