Database Exam 1

Analysts who focus on database

database analysts and data modelers

a view of the entities, attributes, and relationships involved in the world being modeled (conceptual schema) as well as a view of tables, fields, and primary and foreign keys

designer view

software used to manage databases

DBMS

in traditional file processing, each application shares data files

false

a view that makes this person involved in the physical schema only

admin view

person who oversees the projects and managers the personnel

project manager

people who use the applications and database

end users

departmental multi tiered client/server databases

intended for several users, department wide

person who analyzes the business situation and establishes requirements who has technical expertise for overall information systems

systems analyst

person, place, object, event, or concept about which an organization wishes to maintain data

entity

traditional methodology used to develop, maintain, and replace information systems

systems developed life cycle

in independent file processing systems, unplanned duplicate data files are

the rule rather than the exception

Personnel responsible for maintains the database

database admin

Advantages to database systems

program-data independence

reduced program maintenance

better data quality

metadata typically describes all of the following

length

data type

allowable values

which sql sublanguage is used to define database structures such as tables

DDL

Which SQL sublanguage includes operations such as SELECT, INSERT, UPDATE, and DELETE

DML

Which SQL sublanguage is primarily concerned with database privileges and access control?

DCL

in a database table, a row is called a

record

primary purpose of sql

communicating with and managing relational databases

in a database table, a column is also called a

field

what is a schema

a database blueprint describing its structure

multi variate attributes in ER notation

{inside flower brackets}

type of relationship: employee may have no or one parking space

optional 1:1

in data definitions, a fact is

an association between two or more terms

entity instance

a single person, place, object, event, or concept

relationship with a min and max of 1

mandatory 1:1

type of relationship: order must contain one product but may contain several products

mandatory many

a relationship involving two different entity types

binary

identifiers in ER notation

text with a single underline

a relationship between entities of the same entity type is of ___ degree

unary

relationship type: a customer may place zero, one, or many orders

optional many (0,N)

relationship involving three entity types

ternary

a required attribute must have

a value for every associated entity or relationship instance

an identifier (key) is

an attribute or combination of attributes that uniquely identifies an entity instance

strong entities

exist independently of other entities and have its own unique identifier

derived attribute

an attribute whose value can be calculated from related attribute values

[derived attribute square brackets]

composite attributes

consist of multiple component attributes where all components are required

have meaningful component parts or sub attributes

ex: employee address broken into street, city, state, and postal code

ER models represent

objects of interest to the user that will be represented or displayed through the system

an attribute is

a property or characteristic of an entity or relationship type

multivalued attributes

may take on one or more than one value for one entity or relationship instance

descending order query

ORDER BY x DESC

check is a value is missing

WHERE value IS NULL

in query execution, which clause is executed first

FROM

in ORDER BY x DESC, y DESC, what is y?

a tiebreaker when x value is the same

find names that do not match ‘A%’

WHERE name NOT LIKE ‘A%’

find records where a specific value is present

WHERE x IS NOT NULL

filter grouped or aggregate results using

HAVING

round to nearest hundred

ROUND(value, -3)

Which query stacks results and removes duplicate rows

UNION

what role can a subquery in the FROM clause serve

acts as a temporary result table that the other query can use

what must be true for two query results to be combined with a set of operations such as union

they must return the same number of columns with compatible data types

what does query-A EXCEPT query-B

rows from query-A that are not in query-b

set operation the preserves duplicate rows

UNION ALL

in JOINs when join on cols have the same name

USING(col name)

joining on two conditions

ON a.col1 = b.col1 AND a.col2 = b.col2

write a query to create a new schema

CREATE SCHEMA ‘schema_name’ ;

Write a query to create a new table with two columns

CREATE TABLE ‘schema’.’table_name’ (

‘col_1’ DATA_TYPE NOT NULL,

‘col_2’ DATA_TYPE NULL

PRIMARY KEY (‘col_1’));

Write a query to select all columns from a table

SELECT * FROM table;

How do you access the ER diagram of your database?

reverse engineering

How do you apply changes from the ER diagram model to the database?

forward engineering

How do you manually add data rows without writing a query?

SELECT * FROM table;

In the query results, you can manually add new rows of data & click APPLY

Many to many relationships require

an intermediary child table from the parent tables defining the relationship

Ex: many students enrolled in many courses requires “enrolled-in” relation between “student” and “course”

How do you add a column to a table?

ALTER TABLE schema.table

ADD col_name datatype;

How do you remove a column from a table?

ALTER TABLE schema.table

DROP COLUMN col_name;

How do you rename a column in a table?

ALTER TABLE schema.table

RENAME COLUMN old_name TO new_name;

How do you change the datatype of a column in a table?

ALTER TABLE schema.table

MODIFY COLUMN col_name datatype;

How do you insert values into a table?

INSERT INTO schema.table(col_1, col_2, col_3, …)

VALUES (col1_val, col2_val,...), (col1_val, …);

How do you change all values in a column?

UPDATE schema.table

SET col_name = value;

Definition Language (DDL)

Defines and modifies the database schema/structure.

CREATE, ALTER, DROP

Which sublanguage is CREATE, ALTER, DROP?

DDL

DML: Data Manipulation Language

Modifies the actual rows/records inside tables.

INSERT, UPDATE, DELETE

Which sublanguage is INSERT, UPDATE, DELETE?

DML

DQL: Data Query Language

Fetches and reads data without changing anything.

SELECT, FROM, WHERE, UNION

Which sublanguage is SELECT, FROM, WHERE, UNION?

DQL

Display Title and Price for books costing more than $30

Display each unique Genre from Book (no duplicates).

Display FirstName, LastName, and Age for customers aged BETWEEN 21 AND 2

Display FirstName and LastName for customers whose LastName starts with M

Display FirstName and LastName for customers whose LastName has exactly five letters and ends with “tone”

or just _tone bc _ is exact

Count how many different cities appear in Customer. Label the result UniqueCities.

What order do SQL queries execute in after they’re written?

How do you prevent errors in DDL when tables don’t exist?

DROP TABLE IF EXISTS table_name;

How do you add a foreign key to an existing table?

ALTER TABLE table_name

ADD CONSTRAINT fk_name

FOREIGN KEY (col_name)
REFERENCES ref_table(ref_col);

Dropping tables with relationships

a referenced table cannot be dropped while another table depends on it.

delete the dependent table first
ex: if student references department, drop student first

Insert rows from one table into another

Delete rows from a table

Delete all rows from a table without deleting the table or its structure

TRUNCATE TABLE table_name;