Voc Flashcards
Why do we need a DB
Avoid redundancy
data integrity
referential integrity
deletion anomalies
data integrity
refers to the validity of data
avoid redundacy
avoid duplication of information in multiple table within the DB
deletion Anomalies
deletion of one of a table results in the deletion of unintended information
Referential integrity
ensures that relationships between table remain consistent.
cant add a record to the table that contains the foreign key unless there is a corresponding record in the linked table.
redundacy
duplication of information in multiple tables
insertion anomoly
user is unable to insert a new record when it should be possible to do so
deletion anomoly
record is deleted other information tied to the is also deleted
update anomoly
record is updated but other appearances of the same item is not updated.
unified modeling language
used to describe conceptual view of the database
relational model
formal model of the database
relational algebra
a formal language used to symoblically manipulate objects of the relational model
tabel model
informal set of terms for relational model objects. terms used to refer to the physical view of the database
structured query language
is used to build and manipulate relational databases. based on relational algebra. its is declarative. stadard but some products very on implementatin.
uml class or entity
anything in that is to be represented in the db
first step of building a db
model it in natural language
attribute
is a piece of inofrmation that characterizes each member of a class
descriptive atttributes/ natural attributes
those which actually provide real world information about the class. id numbers are not descriptive
class diagram
shows the class and its attributes
relational scheme
starts with the attributes from the class diagram.
sets
domain of all the attributes that belong to a class.
Create table nameTable()
creates a table; CREATE TABLE customers ( cfirstname VARCHAR(20) NOT NULL, clastname VARCHAR(20) NOT NULL, cphone VARCHAR(20) NOT NULL, cstreet VARCHAR(50), czipcode VARCHAR(5));
data models
conceptual, logical, physical
rows
each instance of the class is represented by a row.
tuple
each row is defined in a relational database by a tuple. assigning values to each col.
atomicity
prinicple that each tuple only has one piece of data
example of assignments in tuples
insert into customer (cfirstname, clastname, cphone, cstreet, czip)
values (‘tom’, ‘jewet’, ‘714-555-5555’, ‘100200 slater’, ‘92045’);
example update
update customers set cphone=714-555-4444’ where cphone=’714-555-5555’;
uniqueness
each row is unique no duplicates
unordered
we can display them in any order we wantand the meaning does change. we can sort by whatever we want.
filter
we can include subsets of rows by specifying criteria
superkey
each row must be unique so a set of attributes in each relation scheme mus guarantee uniqueness.
primary key
a unique identifier of each row. picked from the super keys
example add primary key
alter table customers add constraint customer_pk primary key (cfirstname, clastname, cphone);
example of ensuring uniqueness with one statement.
CREATE TABLE customers ( cfirstname VARCHAR(20) NOT NULL, clastname VARCHAR(20) NOT NULL, cphone VARCHAR(20) NOT NULL, cstreet VARCHAR(50), czipcode VARCHAR(5)), CONSTRAINT customers_pk PRIMARY KEY (cfirstname, clastname, cphone);
association/ relationship
the way that two classes are functionally connected to each other.
mulitplicity/cardinality
how few or how many of one class is connected to another class
foreign key
the primary key of the parent association
one side is always the parent. and provides the pk attributes to be copied. the many side is the child
pk get copied to child as fk mando if
the relationship is mandotory. if its a 1 to * relationship. and information is needed to uniquely identify child
identifying
attibutes must be not null
referential integrity
ensuring that every fk is connected to the right primary key of the parent.
identifying2
if the foreign key is part of the primary key then its identifying
non identifying
if fk is not part of the primary key
car example. driver can exist without a car and a car can exist without a driver.
candidate key
minimal super key; meaning if you take one attribute away from the set it is no longer unique
surrogate Pk
a single small attribute such as a number that has no descriptive value such as an id number
substitute pk
a single small attribute such as an abbreviation that has at least some descriptive value
3 rules for substitute and surrogate key addition
- have at least one candidate key
- is a parent
- no candidate key small enough for its values to be copied many times into child table. ( to big)
external key
already defined by someone else surrogate keys zip codes isbn upc
social security
only use as non key feild if required by law
discriminator attribute
or partial key) of a weak entity set is the set of attributes that distinguishes among all the entities of a weak entity set on one particular strong entity.
normalization
following a procedure or set of rules to insure that a database is well designed. most normalization is to eliminate redundatnt data
subkeys always result in redundancy
eliminate it for 3rd normal form
signs that you have multiple values attribute
variable length is long
plural
values have a delimiter,
pay attention to order
ensureing data integrity
values entered in each field of a table is consistent with its attribute domain.
validation rule
constraints on the type of data inserted into col
lookup table
tables created for the pupose of storeing enumerated domains
enumerated domains
attribute domains that may be specified by a well defined reasonable size set of constant values
data definition language (ddl)
used to build and modify the structure of your tables and other objects in the database
data manipulation language (dml)
used to work with data in tables
data dictionary
contains all information about object in your schema
data types
specifies the type of data that an attribute is
cross join
in set theory is a cartesian product
