Midterm 1 Flashcards
Data
data are facts that can be accessed
information
data accessed for a particular purpose
metadata
data that describes the structure of data
dbms vs database system
dbms is for crud and database maintenance
database system is a computer based system whose purpose is to enable an efficient interaction between users and information captured on the database
direct vs indirect interaction
directly interacting w database vs interacting with a front end application
database development steps
conceptual modeling
logical modeling
database implementation
developing front end applications
database deployment
database use
database administration and maintenance
step one db development
requirement collection definition and visualization (ER )
step two database modeling
creates model implementable by dbms software
ex
entity
attribute 1
attribute 2 (FK) connnects to entity 2
step two database modeling
creates model implementable by dbms software
ex
entity
attribute 1
attribute 2 (FK) connnects to entity 2
types of sql language
ddl
dml
tcl
session control statements
system control statement
embedded sql
ddl ?
data definition language
create alter drop revoke grant analyze audit comment
dml?
data manipulation language
call delete merge update insert select, explain pan, lock table,
tcl?
transaction control language
commit, rollback , savepoint, set transaction set constraint
session control statements
alter session, set role
system control statement
alter system
embedded sql statements
ddl dml and transaction control statements within a procedural language program
step 3 database design
database implementation
using a dbms to implement the database model as an actual database (sql)
what is er modeling
conceptual database modeling technique
enables structuring and organizing of requirements collection process and provides a way to graphically represent
er diagram
serves as blueprint for database
two basic constructs entity and relationship
what are entities
constructs the database keeps track of
represented by a rectangle
entity instances
occurrences of an entity
not depicted in er diagrams
ex: student entity sanji occurrence
attribute
characteristic of an entity
represents the details that will be recorded about an entity
within an entity attributes must have different names
unique attribute
attributes whose value is unique for each entity instance
each entity must have atleast one unique attribute
types of db models
conceptual
logical
physical
conceptual model
high level static model that represents business concepts and structures. It’s used to create a shared understanding and communicate ideas
logical model
adds more detail to conceptual model defining entities attributes relationships and keys. identifies potential issues in existing systems and serves as blueprint for physical model
physical model
a model that translates the logical model into technical specifications for a specific database system.
presents physical db structure and is ready to implement
sql command categories
ddl dml dcl (grant revoke)
data control language
tcl
ddl function
create and modify structure of db
dml function
insert modify and retrieve data
update
delete
select
dcl function
used for data access control
why normalize
performance
storage efficiency
maintenance overhead
data consistency
update anomalies
inconsistent data due to multiple copies of the same info
insert anomalies
difficulty adding new data bc of interdependencies between fields
delete anomalies
unintended loss of critical data when related data is deleted
normalization
process used to improve the design of relational databases
1NF
eliminate repeating values
must have atomic (indivisible values)
book a, book b
book a
book b
1NF rule
each table column should contain unique atomic data and each record should be unique
2NF
split into related tables
ex;
order is is not uniquely identifiable so instead move order id and customer name to a separate table
2nf rule
split table into two related tables
eliminate partial dependencies (all non key attributes are fully dependent on entire primary key)
3nf
eliminate transitive dependencies
All non key attributes depend only on primary key
split so title author price and then order i’d title quantity
denotmalizatiok
reversing normalization by joining normalized relations to improve query performance
relational database model
logical database model that represents a database as a collection of related tables
relation
table in relational database
mapping composite attributes
just include the attribute names and not the composite part
hair - color, length in a table will just be color and length
unique composite mapping
underline the attributes in the relation
entity integrity constraint
no primary key can have null values
foreign key
column in relation that refers to a primary key column in another relation
in relational schema the fk points to the pk it came from
referential integrity constraint
in each row of a relation containing a foreign key the value must either match a value in the primary key of the referred relation or be null
candidate keys
a column that may be chosen as a primary key
only map one and others can be denoted with a u
mapping derived attributes
represented with a dashed line
not mapped in relational schema
implemented in front end db
double circle attr
may contain multiple values
mapping unary relationships
the relationship is a foreign key that points to the primary key in the same relation
implicit constraints
each relation must have diff name
in relation:
each column has different name
each row unique
domain constraint
single valued rows
order is irrelevant
primary key
no null pk
user defined constraint
added by database designers
example one column can be null
