PCS Modeling Data (MCQ)

define theory of what is needed with written explainations (typically a meeting among users and main developers or team leads)

• devise pictorial veiw of needed application features
• will create Function Requirement Documnt(FRD)
• typically a meeting with devopers, project managers, and techs

development> QA > Staging/UAT(sample users)> Production (public)

straight shot through the process, resulting less with user input

do circles in process until users are satisfied with product

time limited meetings each day by scrum master

mix of scrum and agile methods

the current database in use for users, holds only current info or liimited historical

Separate database holding historical data, used for business decisions and trends

• RDBMS - Oracle, SQ, Sybase, DB2
• DBMS - Excel, Flat/Text files

• Rows = tuples
• Column Labels = Attributes (simple/composite/single/multi-valued)
• Table = Entity (strong/weak)
• Cloumn Data = Domain

the structure described in formal language supported by DBMS (database management structure)

the blueprint of coding from using DBMS

paper rough draft model, find relations

paper, develop constraints, normalization, ER tool/visio

implemented in server to be tested

unique identifier row, no nulls, only one primary key allowed per table

key in another table that refers to the primary key of another

a set of attributes st. the relation does not have repeated tuple with the same values; thus leads to improper subset in the relation

Used in OLAP to play the role of primary key, automated sequence

verify data (type, range, etc.)

a define value to fill in null areas

the allowance of nulls

parent to child/employee to spouse

Employee to children

broken into conjunction table with composite primary keys

set max/min relationship limit

number of entities in relationship

Entity integrity involves the structure (primary key and its attributes) of the entity. If the primary key is unique and all attributes are scalar and fully dependent on the primary key, then the integrity of the entity is good. In the physical schema, the table’s primary key enforces entity integrity.

In relational theory terms, a domain is a set of possible values for an attribute, such as integers, bit values, or characters. Domain integrity ensures that only valid data is permitted in the attribute. Null ability (whether a null value is valid for an attribute) is also a part of domain integrity. In the physical schema, the data type and null ability of the row enforce domain integrity.

is a subset of Domain Integrity that all its references are valid. In the context of relational databases, it requires that if a value of one attribute of a relation references a value of another attribute, then the referenced value must exist.

Simple business rules, such as a restriction to a domain, limit the list of valid data entries. Check constraints are commonly used to enforce these rules in the physical schema. Complex business rules limit the list of valid data based on some condition. For example, certain tours may require a medical waiver. Implementing these rules in the physical schema generally requires stored procedures or triggers.

• naming schema
• <dbname>.<schemaname>.<tablename>

create a version of table with only needed data(filtered window)

a set of SQL queries that can be saved and called

similar to stored procedure with different properties

act as constraints during certain events

map data and improve query performance

aka pointers; must be opened and closed when working with data

• DDL - Create, Alter, Drop
• DML - Insert, Delete, Update, Truncate
• DQL - Select, From, Where, Group by, Having, Order by
• DCL - Revoke, Deny, Grant

returns distinct values from left query that are not found on the right

Returns distinct values found in both queries

Combines unique results from both queries into single result set

combines all results from both queries, including duplicates

1,1,3,3,5

1,1,2,2,3

breaks up data in random order and ranking

• 1,2,3,4,5
• ranking based on table locationgoing down rows