Tdp - Ccs

Front 1

Business Analyst

Back 1

(Nontechnical) Adds bridge between business users/clients and the team. They bring the job to the team. Stay in all the stages of the project, at each stage the business analyst should communicate with the business users with updates (time, progress)

Front 2

Data Analyst (stream)

Back 2

Technical/Nontechnical position. A person who will analyze the data that you should put in your mission. ~ They will know what will be the system specifications, how much space is required. Model the data, give that data a structure and put it in your database, give you clean data.

Front 3

Developers (Front/backend)

Back 3

Querying, store data, backbone of the company. Front end give structure of how the project should look.

Front 4

Database Administrator (DBA)

Back 4

Administrators of the team, they are developers as well as developers that can handle administrative stuff. DBA do installations, resolve issues.

Front 5

Project Manager

Back 5

can be technical, not necessary, in charge of overseeing the whole project, manages timeframes, overall boss

Front 6

Team Leader

Back 6

in smaller companies, he/she is a project manager

Front 7

Testers (QA)

Back 7

Involved in each phase, but mostly the testing phase. Thoroughly check the developer's work. Report issues back to the developer(s).

Front 8

Development

Back 8

basic, developers playground, developers can do w/e they want.

Front 9

Testing

Back 9

Test product that was sent by the developer

Front 10

Staging/Pre-production

Back 10

involving the user to the test the product, this is called UAT(User Acceptance Testing)

Front 11

Production

Back 11

Live environment, database is up-to-date. Staging may not have up-to-date information (couple days, or week late).

Front 12

SDLC

Back 12

How a project is modeled to its finished product.

Front 13

Gathering requirements (business requirements)

Back 13

BA’s are responsible to gather the business requirements from the business users. 3 most important people BA,PM (deadlines), TL(talk in technical, decide whether project is feasible or not) involved in team meetings. Different meetings: JRD or JAD.

Front 14

JRD

Back 14

joint requirement development (required) Discuss among the business users to get requirements. The output of this meeting business requirement document (BRD) prepared by the BA. Storybook, will have all the details.

Front 15

JRD

Back 15

joint requirement development (required) Discuss among the business users to get requirements. The output of this meeting business requirement document (BRD) prepared by the BA. Storybook, will have all the details.

Front 16

JAD

Back 16

Joint application development meetings discussions of higher technical terminology on how the product can be done. Entire team is included except business users. Outputs: (FRD) Functional requirement document, (SRS) System Requirement Specification, both these documents are created DA & Devs. FRD = Pictorial representation of BRD. SRS = all the details are specified for new software.

Front 17

JAD

Back 17

Joint application development meetings discussions of higher technical terminology on how the product can be done. Entire team is included except business users. Outputs: (FRD) Functional requirement document, (SRS) System Requirement Specification, both these documents are created DA & Devs. FRD = Pictorial representation of BRD. SRS = all the details are specified for new software.

Front 18

(Business) Analysis

Back 18

DA will check if the data fits, all information is checked. Whatever data is coming in will not affect the existing data.

Front 19

Design

Back 19

Structure. Designing how the product should look like.

Front 20

Development

Back 20

Development of the product

Front 21

Testing

Back 21

Thorough testing

Front 22

UAT (User acceptance Testing)

Back 22

UAT, any person outside of the team/company will be a user.

Front 23

Production Release

Back 23

Live Environment

Front 24

Business Process

Back 24

Different ways we use SDLC

Front 25

Waterfall Model

Back 25

Linear, sequential design process. Each phase must be completed in order to move on to the next phase. There is no reversing from previous stage. Used when: Requirements are very well documented. No ambiguous requirements.

Front 26

Scrum (standup) meetings

Back 26

Day-to-day meeting with only the developers

Front 27

Conceptual Phase

Back 27

All basic requirements are gathered, documents are done(JRD, BRD). Create ER Diagram draft, will need to refined (iterative process) until accurate. Output = finalized ER diagram.

Front 28

Logical Phase

Back 28

Cannot modify final ER diagram. Identify key attributes and constraints (all key constraints). Normalization. Language: Tables, columns. Output = normalized ER diagram.

Front 29

Physical Phase

Back 29

Work on any data modelling tool. MS Visio is expensive. ERwin is cheap, has new features. Translate from paper to computer (design canvas). Forward engineer to achieve physical instance (tables, columns).

Front 30

Forward Engineering

Back 30

brand new project, conception, logical to physical (1->2->3)

Front 31

Reverse Engineering

Back 31

there is already an existing database, when changes are required to the existing system, moving from the physical phase to logical phase. Find out the relationships between the tables, find constraints.

Front 32

Tuple

Back 32

rows in a tables.

Front 33

Domain

Back 33

set of valid values for an attribute, datatype of a particular attribute (e.g. varchar, int)

Front 34

Null

Back 34

absence of ANY value

Front 35

Relationship

Back 35

How entities relate to one another

Front 36

Degree

Back 36

how many entities in a relationship

Front 37

Unary

Back 37

only one entity involved in the relationship

Front 38

Binary

Back 38

two entities involved

Front 39

Ternary

Back 39

three entities involved

Front 40

Cardinality

Back 40

Measure of participation

Front 41

Primary key

Back 41

unique identifier, one primary key per table, SQL automatically creates a unique clustered index

Front 42

Foreign key

Back 42

points to PK

Front 43

Candidate Key

Back 43

key to be used in place of PK

Front 44

Composite Key

Back 44

key made of multiple keys

Front 45

Unique key

Back 45

Unique identifier, unique key can be NULL, more than one, up to 249

Hint 45

SQL 2005, up to 999

Front 46

Surrogate key (OLAP)

Back 46

Data warehouse PK (OLTP it’s called Primary Key)

Front 47

Alternate key

Back 47

any candidate keys not being used

Front 48

Weak Entity

Back 48

Dependence on another Entity (e.g. employee’s spouse cannot stand by itself if employee leaves company)

Front 49

One-Mandatory

Back 49

Unary

Front 50

Many-Mandatory

Back 50

One to Many

Front 51

One-Optional

Back 51

One entity may or may not have a relationship with another entity.

Front 52

Many-Optional

Back 52

Many entities may or may not have a relationship with some entities.

Front 53

Surrogate key (OLAP)

Back 53

(PK in OLTP) Surrogate key is an auto generated integer column.

Front 54

Default

Back 54

defaults a value

Front 55

Volumetrics

Back 55

Initial Row Count max rows growth by month

Front 56

Use Inherited constraint

Back 56

If someone is trying to enter any data other than current it will reject it.

Front 57

use inherited default value

Back 57

Null, some other value

Front 58

Entity Integrity

Back 58

Strong PK with no Nulls or Repeats (must be unique).

Front 59

Domain Integrity

Back 59

Correct and proper data type. Domain integrity specifies that all columns in a relational database must be declared upon a defined domain (set of allowable values).

Front 60

User-Defined Integrity

Back 60

Rules or constraints applied by the user to maintain rules of design, which do not belong to the entity, domain and referential integrity categories.

Front 61

Referential Integrity

Back 61

Each and every parent may or may not have a child, every child must reference to a parent. Every FK must refer to a PK or be null.

Front 62

SQL

Back 62

Structured Query Language

Front 63

SQL

Back 63

Structured Query Language

Front 64

ANSCI SQL

Back 64

(1970) General SQL, framework, SELECT, DELETE commands

Front 65

PLSQL

Back 65

Oracle

Front 66

T-SQL

Back 66

Transact SQL (Implicit transactions)

Front 67

SSMS SQL Server Management Studio

Back 67

Not the database, application used to access database engine.

Front 68

Database Engine

Back 68

The actual core part of the RDBMS. Capable of processing and executing the queries, managing the data. DE is somewhere on the hard drive installed. The front end (Microsoft SQL Server Management Studio) allows you to write /communicate/access with the database engine. Server type: [SSRS, SSIS]

Front 69

SQL Server Authentication

Back 69

Not only do you need to get into windows you need a separate key/pw to get authorized to the SQL server.

Front 70

Hierarchy = SQL Server

Back 70

(name) -> Databases -> Schema ->Tables -> Columns

Front 71

go

Back 71

a batch terminator. Used to break down the script into multiple batches. used to separate batches from scripts, before the statement the first batch will be executed, and after the statement will be executed after. Recommended to use for DDL

Front 72

DDL Statements (Data Definition Language)

Back 72

Create, Alter, Drop

Hint 72

You are defining the structure of the table

Front 73

DDL Statements (Data Definition Language)

Back 73

Create, Alter, Drop

Hint 73

You are defining the structure of the table

Front 74

METHOD 1

Back 74

In this method we are creating stand alone table first and then issue Alter Table statements for creating constraints. This method should be used when there are a lot of tables to write and you can’t determine the order of the tables (based on the FKs)

Front 75

<> <> <

Back 75

null/not null>> ,...)

Front 76

alter column <> <> <

Back 76

null/not null>>

Front 77

add <> <> <

Back 77

null/not null>>

Front 78

METHOD 2

Back 78

In this method constraints are created while creating the table (while defining the column itself: column level). Developer need to follow a particular order for table creating, that means there happens to be code dependency. We don’t have any control over the constraint names, constraint names are automatically assigned by SQL Server

Front 79

Data Manipulation Language (DML)

Back 79

Manipulation INSERT, UPDATE, DELETE

Front 80

delete is slower DML statement, DML

Back 80

Insert/Update/Delete are detailed logged

Front 81

select predicate

Back 81

columns that come after the “select” statement

Front 82

search predicate

Back 82

after the “where” clause

Front 83

1 search predicate

Back 83

internally this is faster than using a where clause with two search predicates occurrences of any string or digit.

Front 84

_

Back 84

one occurrence of any string or digit

Front 85

^

Back 85

(called carrot) negation (negative)

Front 86

[]

Back 86

range

Front 87

Regular expression

Back 87

any formula, that you pass to a string ~~

Front 88

order date

Back 88

current date

Front 89

System functions

Back 89

predefined functions

Front 90

where

Back 90

filter databases with non-aggregate columns

Front 91

having

Back 91

filtering based on aggregate columns

Front 92

tempdb

Back 92

used by SQL server as a scratch pad, for sorting, grouping, the tempdb will hold the information. Where statements result will go first to the tempdb, then group by, then having predicates. You can create tables in tempdb for temporary work. These will

Front 93

Inner Join

Back 93

Inner Join will get you all matching records from left table and right table based on the joining condition.

Front 94

Outer Join

Back 94

Non-matching records All non-matching records from the the left table [with the corresponding NULLs on the right table]. RLOJ = LOJ

Hint 94

IJ All non-matching records from the the right table [with the corresponding NULLs on the left table]. RROJ = ROJ

Front 95

RFOJ = FOJ

Back 95

IJ

Front 96

Self Join (logical)

Back 96

If the left and the right table happen to be the same table, meaning if you are joining the table with itself, regardless of which type of join you use, is called a self join.

Front 97

Nested Subqueries (Faster)

Back 97

No correlation from the subquery to the outer query, the subquery can relate..

Front 98

Correlated Subqueries (Avoid, performance very slow compared to nested)

Back 98

if you are writing a query that depends on another query then the entire block is correlated.

Front 99

union

Back 99

remove the duplicate records

Front 100

union all

Back 100

keeps the duplicate

Front 101

except

Back 101

first result set minus the second result set

Front 102

intersect

Back 102

only common records

Front 103

table

Back 103

permanent table

Front 104

view

Back 104

permanent virtual table

Front 105

DCL

Back 105

Data Control Language

Front 106

Regular View

Back 106

It is a type of view in which you are free to make any DDL changes on the underlying table. In this view, if you make some DDL changes on the column(s) which are referred by a view, view becomes inaccessible and becomes an orphan view. You can also do any DML operations on the underlying table.

Front 107

Schema-binding table

Back 107

It is a type of view in which the schema (columns) of a view is physically bound to the schema of the underlying table. This means you are not allowed to perform any DDL changes or operations to the underlying table, only on the columns which are referred in the view structure. All the columns which are not referred by the view structure from the base table can still undergo DDL changes. DML operations are ok on the underlying table.

Front 108

Indexed/Materialized View

Back 108

Is created when you create a unique clustered index on a schema binding view. One copy will be in the base table the other in the index structure. It has its own data, not a virtual table; a physical, permanent table. If you create a unique clustered index on a schemabinding view that particular view is called indexed/materialized view. Once you create an index view it no longer qualifies to be a virtual table but actually holds data rows. This means there are two copies of same data, one in the underlying table and one in the indexed view.

Front 109

Written Exam

Back 109

writing queries on paper (create/alter/join/subqueries)