Term
|
Definition
| Files and folders (flat files) |
|
|
Term
|
Definition
| If you are able to store all the information into two-dimensional tables, you are able to maintain the relationship between the tables, then it is RDBMS. (Relation means tables, no txt, docx, etc) |
|
|
Term
|
Definition
| Easier to manipulation, faster, authorization, no redundancy, scalability |
|
|
Term
|
Definition
| Expensive (budget oriented), have to know mysql (how to manipulate) |
|
|
Term
| Entities, Attributes, Tuples or Instances, Key attribute in Conceptual and Logical Design is (four blank) in Physical database design |
|
Definition
| Tables, Columns, Rows or Records, Primary key |
|
|
Term
| Tables, Columns, Rows or Records, Primary Key, Foreign Key in Physical database design is (five blank) in Conceptual and Logical design |
|
Definition
| Entities, Attributes, Tuples or Instances (instance may be used in physical design), Key attribute and N/A |
|
|
Term
|
Definition
| A process to follow that design the database from scratch (may not be scratch if already exists) |
|
|
Term
|
Definition
| It is a container which has set of tables which actually stores the data |
|
|
Term
|
Definition
| Primary key is a column or a set of columns (a composite primary key) that uniquely identifies each row or record in that table. No NULL and No repeating values (must be unique). Only one primary key per table. |
|
|
Term
|
Definition
Seek faster than scan
with index, without index
index improves the speed of searching
like book |
|
|
Term
When a primary key created, ...
How many clustered index per table?
How many non-clustered index per table? (depend on 2005 or 2008) |
|
Definition
Microsoft automatically creates a unique clustered index. A wide clustered index if a composite primary key. Only one clustered index per table. Non-clustered index for other columns (unique)
249,999 |
|
|
Term
| What primary key used for? |
|
Definition
| Primary keys are used to maintain the relationship between multiple tables |
|
|
Term
|
Definition
| It is a column in a table that references a primary key or unique key from other table. |
|
|
Term
| What primary keys and foreign keys used for? |
|
Definition
| Primary keys and foreign keys are used to maintain the relationship between multiple tables in the form of referential integrity. |
|
|
Term
| Four different types of relationship |
|
Definition
One to many
Many to many
One to one
One to fix cardinality |
|
|
Term
| Constraint for foreign key? |
|
Definition
| Foreign key must point to Primary key or Unique Key. Repeating values? Yes! Foregin key has to exist in that reference Primary key or Unique key |
|
|
Term
| Referential integrity (exists in one to many)? |
|
Definition
Applies for different types of relationship in the form of a parent table and child table. Referential integrity must follow the following rules:
Rule 1: For inserting a record in the child table, the corresponding foreign key value must be present in the primary key of the parent table. If not, then it violates the referential integrity.
Rule 2: For deleting a record from the parent table, the corresponding primary key value must be deleted from the child table. If not, then it violates the referential integrity.
We can also use ON DELETE CASCADE to automatic the delete process. This only helps delete process. |
|
|
Term
| Benefit of Referential integrity? |
|
Definition
| Less amount of redundant data and inconsistent data |
|
|
Term
| Some difference between Primary key and Foreign key |
|
Definition
Foreign key could have null values. Occurs when know child information ahead of parent information. You responsibility to update, otherwise creates inconsistent data.
Foreign key doesn't create index
Foreign key can repeat (mean to repeat) |
|
|
Term
| Many to many in conceptual and logical is _ in physical |
|
Definition
| One to many plus many to one in physical |
|
|
Term
| What is many to many relationship? |
|
Definition
Explain with examples.
Like student and course |
|
|
Term
| How to implement many to many relationship in physical design? |
|
Definition
| Conjunction table the only way. (I made the following) Two foreign keys referenced to two parent primary keys and them together creates the composite primary key for the child table |
|
|
Term
|
Definition
|
|
Term
| One to one is a subset of _ and _ and how it works with the first one? |
|
Definition
| A subset of one to many also a subset of one to fixed cardinality. Check constraint with cap of 1 (later) parking no foreign key EID only exists at most once. |
|
|
Term
| What is the cardinality of a relationship? |
|
Definition
| The number of repeating instances in the child table respect to the instances in the parent table is called the cardinality of a relationship. Check constraint with cap of a fixed number |
|
|
Term
| Unique key automatically creates _ |
|
Definition
| Unique Non-Clustered Index |
|
|
Term
| Constraints for Unique Key |
|
Definition
Maximum one NULL per column -> NULL has to be unique as well.
Can have multiple Unique keys in a table |
|
|
Term
| Reason to use Unique Key? |
|
Definition
| Allow multiple unique keys in a table |
|
|
Term
| Keys in Physical design? (3) |
|
Definition
|
|
Term
|
Definition
| Surrogate Key, Candidate Key, Alternate Key |
|
|
Term
What is DW OLAP and DB OLTP?
What key is used in DW OLAP? |
|
Definition
-- DW (Data Warehouse) OLAP (Online Analytical Processing)
-- Historical Data (for analyze)
Identity column -- auto generated column based on seed value and incremental value must be Numeric (by SQL server)
-- DB OLTP (Online Transaction Processing)
Responsible for current data
Surrogate Key is used in DW OLAP |
|
|
Term
|
Definition
| Qualified as a Primary Key, but is currently not using as a Primary Key |
|
|
Term
| Relations between surrogate key, candidate key and unique key |
|
Definition
Surrogate Key is a Candidate Key, Candidate Key does not necessarily be a Surrogate Key
Unique Key is not a Candidate Key |
|
|
Term
|
Definition
| Only one candidate key, other than that, other candidate keys are actually called alternate keys |
|
|
Term
Design from scratch
Design levels of a database |
|
Definition
| Conceptual->Logical->Physical |
|
|
Term
| Everything about JRD Meetings like whos gonna meet, what is made, at the last JRD, what is signed... |
|
Definition
JRD Meetings (Joint Requirement Definition) (Non Technical) What??
-- Client
-- CTO. Project Manager, Team Lead, StakeHolders, Investors
-- Business Analyst (Bleach between technical and nontechnical)
Business Analyst makes BRD’s, BC (Business Contract), FRS (Function Requirements Specification), SRS (Software ...) ...
After last JRD, MSA(Master Service Agreement) |
|
|
Term
| About JAD Meetings like whos gonna meet, what is made, at the first JAD... |
|
Definition
JAD Meetings (Joint Application Development) (Pure Technical Meetings) How??
-- Business Analyst
-- Developers, Team Lead, Project Manager (may not be present), CTO (may not be present), Testers (may not be present)
Take place throughout the life of the project
First JAD, go through what the client want by Business Analyst |
|
|
Term
| input of conceptual design is output of _ ... what we do in conceptual design |
|
Definition
Input of Conceptual design is Output of JRD Meetings
Breaking down non-technical to technical requirements, creating an ER diagram that represents the database the client want. Blue Print on the paper. Accurate ER Diagram on paper |
|
|
Term
| What we do in logical design? |
|
Definition
Identify the key attributes and identify the constraints
Normalizing ER Diagram on paper |
|
|
Term
| What we do in physical database design? |
|
Definition
Choose appropriate RDBMS
To ER diagram on computer
ERwin get the physical instance of the database |
|
|
Term
|
Definition
| Attribute classifies the information in the entity |
|
|
Term
|
Definition
| Association between two or more entities |
|
|
Term
|
Definition
| The repeating instances in child table for instance in parent table |
|
|
Term
| Business Rule has how many constraints in SQL server? And they are? |
|
Definition
7
Primary Key (Unique)
Foreign Key (Referential Integrities)
Unique Key (Unique)
Above are Key Constraints
Below are Column Constraints
CHECK
Nullability
Surrogate Key
Default |
|
|
Term
| How many types of attributes? They are? Definitions? Examples? |
|
Definition
6
Simple VS Composite Attributes
Simple cannot be divided into simpler components
Composite can be split into components and make sense
Different notations for composite attributes
Birthday can be composite = = if need to search by year
Single VS Multi-valued Attributes
Double eclipse for multi-valued
Stored VS Derived Attributes
Dotted eclipse for Derived
Do not need to store Years of service (calculate on the fly) |
|
|
Term
|
Definition
| graphical representation of the database |
|
|
Term
|
Definition
| The number of entities involved in the relationship is called the degree of that relationship. |
|
|
Term
|
Definition
| 1 unary, 2 binary, 3 ternary |
|
|
Term
| How to implement unary one to many in physical design? |
|
Definition
| Foreign key points to the same table's primary key. |
|
|
Term
| How to implement unary many to many in physical design? |
|
Definition
| Conjunction table creates two foreign keys pointing to the same primary key in parent table. |
|
|
Term
| What is a light weight PK? |
|
Definition
| It is a Unique key with not null constraint. |
|
|
Term
| Types of entity and definition? |
|
Definition
Regular VS. Weak entity type
Regular entity (Strong entity) is an entity which has its own key attribute and does not dependent on any Strong entity for its existence and is a direct interest to the organization.
Weak entity
Weak entity is an entity that may or may not have its own key attribute and dependent on some other Strong entity for its existence which is not a direct interest to the organization.
Double rectangle means weak entity
Double diamond means weak relationship |
|
|
Term
| Is there a weak table? Answer is no, so what should we do? |
|
Definition
In the physical design, nothing called Weak table. Your responsibility to convert weak entity to strong entity.
How to convert?
Borrow the primary key from the strong entity to the weak entity as a foreign key along with the key of the weak entity form the composite primary key of this table. |
|
|
Term
| Foreign key is always _ participation, Primary key is usually _ participation |
|
Definition
|
|
Term
| What is total participation and partial participation? Explain with examples (No answer) |
|
Definition
|
|
Term
| If many to many relationship has attribute, _ |
|
Definition
| turn them into columns into the conjunction table. |
|
|
Term
| Transaction takes less than _ seconds in FICAA US Federal rules for building database. Less than _ seconds in HIPAA |
|
Definition
|
|
Term
|
Definition
|
|
Term
| Know how to deal with composite attribute, multi-value attribute, derived attribute |
|
Definition
| Split to several simple attribute (if necessary and make sense), Create a child table, Ignore |
|
|
Term
| Most important part in Logical design? |
|
Definition
|
|
