Data Modeling and T-SQL
Meetings / Methodology
Meeting Types
JRD – Joint Requirement Definition: Define theory of what is needed with written explanations
Will Create a BRD (Business Required Document)
Typically a meeting among users and main developers or team leads
JAD – Joint Application Development: Devise pictorial view of needed application features
Will create FRD (Function Requirement Document)
Typically a meeting with Developers, project managers, and techs
Typical Process of project
Development -> Q&A (Testers) -> Staging/UAT (Sample Users) -> Production (Public)
Meetings do not always go one way. It is very possible to gather the business requirements in a
project, go through analysis, and then begin design only to find out that the users do not like
what is being developed. In this case, things would restart at the business requirements.
Methodology
Waterfall – Straight shot through the process, resulting is less user input
Agile – Do circles in process until users are satisfied with product
Scrum – Time limited meetings each day run by Scrum Master
Spiral – Mix of Scrum and Agile methods
Developing a Database
Types
OLTP – Online Transaction Processing
Is the current database in use for users, holds only current info or limited historical
OLAP – Online Analysis Processing
Separate database holding historical data, used for business decisions and trends
RDBMS & DBMS – Relational Database Management System
RDBMS – Oracle, SQL, Sybase, DB2
DBMS – Excel, Flat/Text files
Data Modeling
Development of a database occurs to shape what will hold the data and how (Data Modeling)
Rows = Tuples / Columns Labels = Attributes (simple/composite/single/multi-valued) /
Table = Entity (strong / weak) / Column Data = Domain
Schemas – The structure described in a formal language supported by the DBMS
j
Concept Phase – Paper rough draft model, find relations
Logical Phase – Paper, develop constraints, normalization, ER Tool / Visio
Physical Phase – Implemented in server to be tested
Business Rules
Primary Key – Unique identifier row, no nulls, only one primary key allowed per table
Foreign Key – Key in another table that refers to the primary key of another
Unique Key – Used in U-NCI, allows 1 null value, 249 in 05 / 999 in 08 per table
Surrogate Key – Used in OLAP to play the role of primary key, automated sequence
Check – Verify data (type, range, etc)
Default – A define value to fill in null areas
Nullibility – Allowance of nulls
Relationships
Relationships occur among entities
1 : 1 – One to One – Parent to child / Employee to spouse
1 : M – One to Many – Employee to Child
M : M – Many to Many – Broken into conjunction table with composite primary keys
1 : Fixed Cardinality – Set max/min relationships limit
Degree
Number of instances of entities in a relationship
Unary – Only one entity in relationship
Binary – Two entities in relationship
Ternary – Three entities in relationship
Domains
The set of all data types and ranges of values an attribute can assume
Allow the following:
1. Verify that the values for an attribute are valid
2. Ensure that various data manipulation operations are logical
3. Help conserve effort in describing attribute characteristics
Integrities
Entity Integrity:
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.
Essentially, entity integrity is normalization.
Domain 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.
Referential Integrity:
A subset of domain integrity, referential integrity refers to the domain integrity of
foreign keys. Domain integrity says that if an attribute has a value, then that value must
be in the domain. In the case of the foreign key, the domain is the list of values in the
related primary key. Referential integrity, therefore, is not an issue of the integrity of
the primary key but of the foreign key.
The null ability of the column is a separate issue from referential integrity. It’s perfectly
acceptable for a foreign key column to allow nulls. Several methods of enforcing
referential integrity at the physical-schema level exist. Within a physical schema, a
foreign key can be enforced by declarative referential integrity (DRI) or by a custom
trigger attached to the table.
User-Defined Integrity:
Besides the relational theory integrity concerns, the user-integrity requirements must
also be enforced, as follows:
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.
Some data-integrity concerns can’t be checked by constraints or triggers. Invalid,
incomplete, or questionable data may pass all the standard data-integrity checks. For
example, an order without any order detail rows is not a valid order, but no automatic
method traps such an order. SQL queries can locate incomplete orders and help in
identifying other less measurable data-integrity issues.
SDLC
The processes listed follow the order of the SDLC or Software Development Life Cycle