Chapters 7 and 9 Flashcards
Into what two broad categories do SQL functions fit into?
data definition language
data manipulation language
creates a database schema
CREATE SCHEMA AUTHORIZATION
creates a new table in the user’s database schema
create table
ensures that a column will not have null values
not null
ensures that a column will not have duplicate values
unique
defines a primary key for a table
primary key
defines a foreign key for a table
foreign key
defines a default value for a column when no value is given
default
validates data in an attribute
check
creates an index for a table
create index
creates a dynamic subset of rows and columns from one or more tables
create view
Modifies a table’s definition (adds, modifies, or deletes attributes
or constraints)
Alter table
Creates a new table based on a query in the user’s database schema
create table as
permanently deletes a table
drop table
permanently deletes an index
drop index
permanently deletes a view
drop view
the process the DBMS uses to verify that only registered users access the database
authentification
schema
logical group of database objects
logical group of database objects
schema
a symbol that can be used as a general substitute for other characters or commands.
wildcard character
wildcard character
a symbol that can be used as a general substitute for other characters or commands.
field of mathematics dedicated to the use of logical operators
boolean algebra
list the six basic data types
number, numeric, char, varchar, date
provides for data collection, storage, and retrieval.
Information system
process that establishes the need for an information system and its extent.
systems analysis
process of creating an information system
systems development
what three factors does the performance of an information system depend on?
database design/implementation
application design and implementation
administrative procedures
process of database design and implementation.
database development
what is the primary objective of database design?
create complete, normalized, nonredundant, and fully integrated conceptual, logical, and physical database models
traces the history of an information system and provides the big picture within which the database design and application
development can be mapped out and evaluated.
Iterative rather than sequential
Systems Development Life Cycle
What are the five steps of the Systems Development Lifecycle?
Planning Analysis Detailed systems designed Implementation Maintenance
Professional, help produce better systems within a reasonable amount of time
and at a reasonable cost. In addition, they are more structured, better documented, and
especially standardized, which tends to prolong the operational life of systems by making them easier and cheaper to
update and maintain.
Computer-Aided systems engineering (CASE)
What are the six phases of the Database Life Cycle (DBLC)?
Database initial study Database design implementation and loading testing and evaluation operation maintenance and evolution
defines the extent of the
design according to operational requirements.
system scope
technique that creates logical representations of computing resources that are independent of the underlying physical computing resources.
virtualization
all database objects are backed up in their
entirety.
full backup (dump)
only the objects that have been updated or modified since
the last full backup are backed up.
differential backup
backs up only the transaction log operations that are not reflected in a
previous backup copy of the database.
transaction log backup
what are the five most common sources of database failure?
software hardware external factors programming exemptions transactions
first stage in the database design process
conceptual design
minimal data rule
all that is needed is there, and all that is there is needed.
what are the four steps of conceptual design?
data analysis and design
entity relationship modeling and normalization
data model verification
distributed database design
document that provides a
precise, up-to-date, and thoroughly reviewed description of the activities that define an organization’s operating environment.
description of operations
is an information system component that handles a specific business function,
such as inventory, orders, or payroll.
module
cohesivity describes the strength of the relationships found among
the module’s entities.
describes the strength of relationships found among the module’s entities
describes the extent to which modules are independent of one another.
Module coupling
true or false, modules must display high coupling
false, the modules must be independent of other modules
a subset of a database that is stored at a given location.
database fragment
the process of determining the data storage organization and data access characteristics of the
database to ensure its integrity, security, and performance.
physical design
stores related rows from two related tables in adjacent data blocks on disk.
clustered tables
is a set of database privileges that could be assigned as a unit to a user or group.
database role
design starts by identifying the data sets and then defines the data elements for each of those sets.
This process involves the identification of different entity types and the definition of each entity’s attributes.
top-down database design
design first identifies the data elements (items) and then groups them together in data sets. In
other words, it first defines attributes, and then groups them to form entities.
bottom-up design
productive when the data component has a relatively small number of objects and procedures.
centralized design
design might be used when the system’s data component has a considerable number of entities and
complex relations on which very complex operations are performed.
decentralized design
Provides for data collection, storage and retrieval
information systems
What are information systems composed of?
people hardware software Databases, applications procedures
Process that establishes need for and extent of information system
systems analysis
process of creating information system
systems development
What are the three steps of database design?
conceptual
logical
physical
designs a database independent of database software and physical details
designed as software and hardware independent
Conceptual data model
Components of Conceptual Design
Describes main data entities, attributes, relationships and constraints
Conceptual data model
T/F, the minimum data rule applies to the logical design process of database design
F
Conceptual
Verification against proposed system processes
Revision of original desgin
Data model verification
Designs an enterprise-wide database that is based on a specific data model but independent of physical -level details
logical design
How does one validate a logical model?
Normalization
Integrity constraints
Check against user requirements
Process of data storage organization and data access characteristics of the database
physical design
Causes two tables with a common field to be combined into a single table or view
Join operation
A join in which rows that do not have matching values in common columns are still included in the result table is called:
Outer Join
What type of join is this: A report is desired that lists all customers, the total of their orders during the most recent month, and includes customers who did not place an order during the month (their total will be zero)
outer join
a type of query that is placed within a WHERE or HAVING clause of another query is called
subquery
EXISTS is what type of operator
relational set operator
the _____ clause is used to restrict the output of a GROUP BY query by applying a conditional criteria to the grouped rows
partition by
a____ subquery is a subquery that executes once for each row in the outer query
correlated subquery
T/F A join operation causes tow disparate tables to be combined into a single table or view
F
Causes 2 tables with a common field to be combined into a single table or view
T/F A report is desired that lists all customers and the total of their orders is called an outer-join
F
Specifies syntax and semantics of SQL data definition and manipulation
Specifies minimal and complete standards, which permit different degrees of adoption in products
Defines the data structures and basic operations for SQL databases
SQL Standard
Set of commands used to update and query a database
DML
DELETE FROM Customer_T WHERE state=”HI”
Deletes all records from customer_t where the state is equal to HI
Provides rapid random and sequential access to data
Purpose of indexes
In a SQL statement, which of the following parts states the conditions for row selection?
Where
Select* from Customer Where Cust_Type=”Best”
Selects all the fields from the customer table for each row with a customer labeled “Best”
Select Avg(standard_price) as average from product_V
The average standard_price of all products in Product_V
Select Count (product_description) from product_T
How many products have product descriptions in the product table?
Select item_no, description, from item where weight>100 and weight
The item_no and description for all items weighing between 101 and 199
Select driver_no, count(*) as num_deliveries from deliveries where state=’MA’ group by driver_no
A list of each driver who made deliveries to state=’MA’ as well as the number of deliveries that each driver has made to that state
A dependency in which one or more nonkey attributes are functionally dependent on part, but not all, of the primary key is call a _____dependency
Partial functional
A functional dependency between two or more nonkey attributes is called
transitive dependency
When all repeating groups have been removed from a table that has PK, it is said to be in which normal form?
1NF
Purpose of normalization
eliminate redundancies and anomolies
The following query will execute without errors (True/False).
Select customer.customer_name, salesman.sales_quota
From customer
Where customer.salesman_id = (select salesman_id where fname = ‘Arun’);
FALSE
Which query type relies on the outputs of the outer query to run?
Correlated subquery
What do we use to add conditional restrictions to the SELECT statement that limits the rows returned by the query?
WHERE
What is the wild card in SQL Server that can select any kind of characters/numbers and any number of characters/numbers?
%
To specify syntax and semantics of SQL data definition and manipulation
Purpose of SQL standard
This is the dependency that occurs when a non-key attribute determines another non-key attribute.
Transitive dependency
CRSESEC (Course#, Section#, Course name, Location)
FD: Course# > Course Name
1NF
When is a table in third normal form (3NF)?
It is in 2NF and contains no transitive dependencies
What is taking the primary key of the one side and store it as a foreign key in the many side?
This is how you create a relationship.
Atomicity
An attribute that cannot be further subdivided
What is the difference between an inner join and an outer join?
The inner join is the traditional join in which only matching rows are returned. The outer join returns not only the matching rows but the rows with unmatched attribute values for one table or both tables to be joined.
A _______ is invoked before or after a row is inserted, updated, or deleted and is associated with a database table?
Trigger
What is one of the advantages of stored procedures?
They can reduce network traffic, and the same code can be used repeatedly without retyping
How do we join Customer and Salesman Tables and select salesmen whose last name is Gold?
where customer.salesman_id = salesman.salesman_idand salesman.lname = ’Gold’;
What is the abbreviation of Structured Query Language?
SQL
In order to have a HAVING clause, you also often need this clause.
Group By
What is
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY ;
?The order how SQL statements are written.
What is a subquery
This is a query inside a query.
When running a query on two or more different tables, we often have to do this for the query to run properly.
join
R, SQL Server, Tableau, Web Servers, MS Excel, Desktops…
Examples of information systems
What is the purpose of data base design (development)?
In order to create complete, normalized, nonredundant, and fully integrated conceptual, logical, and physical database models
Stage where storage capacity, DBMS vendors, access rights, security measures, performance measures and so on are discussed, selected, and implemented
Physical design of databases
What is the NOT EQUAL sign in SQL Server?
How do we execute a stored procedure?
EXEC
What’s the function for calculating the difference of two dates?
DATEDIFF()
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE = 21344;
we select product description, indate, price, and vender code from the product table where the vendor code is 21344
What is the heart of the organization
data
What are the two opposite goals
when you designing a database?
Efficiency vs. Redundancy
T/F the systems development life cycle is iterative rather than sequential
true
T/F NOT is sometimes considered an arithmetic operator
F it is a logical operator
checks whether attribute values matches given string pattern
LIKE
checks whether attribute value is null
IS NULL
checks whether attribute value matches any value within a value list
IN
checks if subquery returns any rows
EXISTS
clause produces list of only values that are different from one another
DISTINCT
T/F Max and Min are aggregate functions
T
T/F COUNT is not an aggregate function
F
Exists when there is a functional dependence in which the determinant is only part of the primary key
ONLY possible when the PK is a “composite” PK.
Partial dependency
Exists when there are functional dependencies such that X → Y, Y → Z, and X is the primary key
Transitive dependency
n many cases, a database will automatically convert one data type into another when needed. On the other hand, there are instances when that is not the case, or when you want to explicitly specify what data type to change into. In these cases, you can use
CAST function
similar to the CAST function in that they both change the value from one data type to another.
CONVERT
combine together the results from several different fields.
CONCATENATE
used to return a portion of string
SUBSTRING
used to remove specified prefix or suffix from a string
TRIM
used to get the length of a string
LENGTH
returns the current date and time
GETDATE()
SELECT GETDATE() AS CurrentDateTime
Obtaining the current date and time
adds or subtracts a specified time interval from a date.
DATEADD()
removes all space characters from the left-hand side of a string.
LTRIM
returns a character expression with lowercase character data converted to uppercase.
UPPER
Syntax for UPPER
UPPER ( character_expression )
Syntax for LTRIM
LTRIM ( character_expression )
Syntax for DATEADD()
DATEADD(datepart,number,date)
Syntax for TRIM
SELECT TRIM(‘ Sample ‘);
Syntax for CONCATENATE
CONCAT (str1, str2, str3, …)
Syntax for CONVERT
CONVERT (expression, [data type])
Syntax for CAST
CAST (expression AS [data type])
Returns all rows when there is at least one match in BOTH tables
inner join
Return all rows from the left table, and the matched rows from the right table
left join
Return all rows from the right table, and the matched rows from the left table
right join
Return all rows when there is a match in ONE of the tables
full join
Inner join syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
Syntax for LEFT JOIN
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
Syntax for RIGHT Join
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
Syntax for full join
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
used to combine the result-set of two or more SELECT statements.
UNION
UNION syntax
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
vocabulary used to define data structures
Definition Data Language (DDL)
vocabulary used to retrieve and work with data
Data manipulation language (DML)
used to return the results of 2 or more SELECT statements.
INTERSECT
INTERSECT syntax
SELECT expression1, expression2, … expression_n
FROM tables
WHERE conditions
INTERSECT
SELECT expression1, expression2, … expression_n
FROM tables
WHERE conditions;
T/F The feasibility study generally occurs during the analysis phase of the systems development lifecycle
F it occurs during the planning phase
T/F performing existing system evaluation occurs during the analysis phase of the systems development lifecycle
T
T/F Enhancement of the database occurs during the maintenance phase of the Systems development life cycle
T
In the database life cycle, the operation phase generally occurs after the maintenance phase
F it directly precedes it
T/F defining the problems and constraints in the database life cycle occurs during the initial study
T
Who should be thinking about these questions: How must the data be structured?
How will the data be accessed?
How are the data transformed into information?
Database designer
Who should be thinking about these questions:
What are the problems?
What are the solutions?
What information is needed to implement the solutions?
What data are required to generate desired information?
Manager
syntax for correlated subquery
SELECT * FROM t1
WHERE column1 = ANY (SELECT column1 FROM t2
WHERE t2.column2 = t1.column2);
