Final Flashcards
what is a database?
a structured collection of related meaningful data
what is a database managemet system?
tool for accessing a database
- redundancy control
- access control
- storage
- querying
- backup
- concurrent access
- enforcing constraints
what are the layers of abstraction
external (user), conceptual (schemas), internal (data mapping)
what is the entity relationship model?
is a data model designed for the purpose of representing the semantics associated with data from the miniworld. The ERM is used in the conceptual design stage, where the conceptual scheme of the application’s database is conceived
the conceptual scheme that is created based on the entity-relationship model is called what?
Entity-relationship diagram (ERD)
what is an entity?
something in the real world that exists independently
what is an attribute?
information about an entity (ex. gender, dob, city of birth, etc.)
what is a composite attribute?
an attribute composed of parts (ie. address to components)
what is a multi-valued attribute
an attribute that may be composed by a group of values (ie. patient has more than one telephone number)
what is a derived attribute?
an attribute obtained by processing other attributes (ie. age = date - dob)
what is the cardinality ratio?
cardinality ratio specifies the number of relationship instances in which an entity can participate.
(patient HAS sample has cardinality of 1:N ie. one patient can have n sample entitities)
what are the mysql data types?
- int
- varchar
- boolean
- double
- float
- date
- datetime
- char
- text
- enum
insert rows into a table
insert into
how do you specify a primary key
primary key (id)
how do you specify a unique key?
unique key (name)
how to delete a table
drop table name
what is the format of a select
select col_name from tbl_name where ___
what is the not equals sign
<>
what is the form of the update statement
update tbl_name SET col_name = __
where attribute= __
what is a schema? (conceptual schema)
a table set of constraints on data
what is a relation?
a table and all its entries
what is a relation schema?
the set of constraints on the relation
what is a domain constraint
restricts the domain of an attribute (int, varchar, float)
what is a key constraint
requires that entries in a column or combination of columns be unique
what is an entity constraint
primary keys cannot be null
what is a referential constraint
requires that an attribute be present in another table
what is a semantic contraint
rules about the system outside of the database
how do you represent an ER diagram of employees managing departments
[employee] 1 - <> = 1 [department]
what is a foreign key constraint?
a constraint that says an attribute can only contain values that are the primary key of a specified table
what changes can break a foreign key relationship?
- update
- delete
how do you add a foreign key reference
foreign key (cid) references tbl_name (id)
which level of abstraction is the ER model most concerned with?
conceptual (providing a high-level description of data)
which is created first when designing a database, the ER model or the schema?
ER model
the insert command may violate which constraint
- domain
- key
- referential
- all
all of the above
the delete command may violate which constraint
- domain
- key
- referential
- all
referential
what does an inner join join on?
set of matching records on both tables
what is the purpose of “having”
allows you to perform conditional (where) on an aggregate function
ie. having sum(field > value)
SELECT name FROM people WHERE id NOT IN (SELECT p.id FROM people p INNER JOIN people_jobs pj on pj.pid = p.id); yields what?
name of person without job
SELECT name FROM people WHERE id NOT IN (SELECT p.id FROM people p INNER JOIN people_jobs pj on pj.pid = p.id); what would happen if there was a null attribute in the pid column from person_job?
the subquery would return null and not in doesn’t handle null well and will return null
T/F: A foreign key with casade delete means that if a record in the reference table is deleted, then the corresponding records in the referencing table will automatically be deleted
True
