Mid Term study Flashcards
What is the Range of Database applications?
-Personal Databases
-Multitier Client/Server Databases
-Enterprise Applications(
* Enterprise Resource Planning
* Data Warehousing)
What are the THREE development stage models?
Conceptual model
Logical Model
Physical Model
Problems with Data Dependency?
Each application programmer must maintain his/her own data
Lack of Coordination and Central Control
Non-standard file formats
Costs and Risks of Database Approach?
New, specialized personnel
Installation and management cost and
complexity
Conversion costs
Need for explicit backup and recovery
Organizational conflict
What is the Database Approach?
Central repository of shared data
Data is managed by a controlling agent
Stored in a standardized, convenient
form
Requires a Database Management System (DBMS)
What are CASE Tools
computer-aided software engineering Tools
What is a Repository?
centralized storehouse of metadata
What are End Users?
people who use the applications and
databases
Define Metadata?
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.
Define Conceptual Data Modelling
- 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)
Define Logical Data Modelling
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)
Define Physical Data Modelling
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
Select Query syntax
SELECT [column name] from [table name]
WHERE [Condition]
Selecting null or not null syntax
SELECT * FROM [TABLE] WHERE Column name IS NULL
IS NOT NULL
BETWEEN and IN clause examples
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)
LIKE Clause: What do these % and _ mean?
//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%’
Date functions?
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;
String functions?
CONCAT(‘W3Schools’, ‘.com’);
REPLACE(‘SQL Tutorial’, ‘T’, ‘M’);
RTRIM(‘SQL Tutorial ‘)
UPPER(‘sql’)
LOWER[‘SQL’)
SUBSTRING(‘SQL Tutorial’, 1, 3)
//SQL extracted
LEN(‘SQL’)
Mathematical functions
ROUND(235.415, 2)
//round to 2 decimal places
CEILING(25.75)
FLOOR(25.75)
Aggregate functions
MIN()
MAX()
COUNT()
AVG()
SUM()
CAST()
What are business rules?
Basis for the data model.
Define what data does your business needs and define
how to govern the data.
What does the ER diagram consist of?
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)
What are Supertypes and Subtypes?
Supertype is a generic entity type that has one or more Subtype.
Explain the Completeness constraints
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
Explain the Disjointness Constraints
Disjoint rule: A Supertype can only have one subtype
Overlap Rule: A Supertype can have one or more subtypes
Two Approaches to Make Relational Models
Generalization and Specialization
Generalization is a ______-__ approach where we start looking at ______ and then categorize all entities with similar
attributes to ______ _____
Generalization is a bottom-top approach where we
start looking at entities and then categorize all entities with similar
attributes to entity types
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.
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.
What does ACID stand for?
ATOMICITY
CONSISTENCY
ISOLATION
DURABILITY
Explain the A in ACID
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.
Explain the C in ACID
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.
Explain the I in ACID
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.
Explain the D in ACID
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.
What is CAP Theorem
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.
Scale-up vs Scale-out
Scale-up: Vertical scaling, make one machine stronger by adding more resources.
Scale-out: Horizontal scaling, Add more machines to share the load.
Schema on read is?
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
Schema on write is?
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
