Basic Database, SQL Flashcards
What do databases store?
data
means of accessing, updating, manipulating, and analyzing data
What is SQL?
SQL is the standard database language for defining and accessing databses
what does a database system consist of?
- data
- database management software. software that stores and manages data in the database
- application programs. applications programs that present data and enable the user to interact with the database system
what does DBMS mean
DBMS is database management system
Name several database systems
MySQL Oracle (also called Oracle RDBMS) IBM's DB2 and Informix Microsoft SQL Server Sybase
What is a database?
a database is a collection of tables
a database is a repository of data that form information
Who are database management systems designed for?
programmers, not users.
how do ordinary users access and update basebases?
Application programs are built on top of the DBMS for customers to access and update the database
How should we think about application programs?
a database application program is an interface between the database system and its users
what kind of database are most databases?
most databases are relational databases
what is a relational database. name 3 key components
databases based on the relational model.
- structure
- integrity
- language
define “structure” for relational databases
the representation of data
define “integrity” for relational databases
imposed constraints on the data
define “language” for relational databases
the means for accessing and manipulating data
what is a “relation” in a relational database
a relation is a table that consists of nonduplicate rows.
what is a record in a database?
row
what does a column in a relational database represent?
the value of a single attribute
for relational database theory, what is a row called?
tuple
for relational database theory, what is a column called?
attribute
what do tables in a database do?
tables describe the relationship among data
name 3 types of constraints
domain constraints
primary key constraints (intrarelational constraints– a constraint only involves one relation)
foreign key contraints (interrelational– a constraint involves more than one relation)
what is an integrity constraint, and why do we use it?
imposes a condition that all the legal values in a table must satisfy.
define domain constraints
- specify permissible values for an attribute
- domains can be specified using standard datatypes (such as ints, floating-point numbers, fixed-length stings, variant-length strings, etc).
- can impose to narrow ranges, for instance. maybe numberOfCredits field can only be between 1 and 5
define primary key constraint
jjdkd
define superkey
an attribute or set of attributes that uniquely identifies the relation. distinct set of tuples (rows).
a superkey is a combination of columns that uniquely identifies any row within a relational database management system table.
customer information:
customer name customer id social security number address date of birth
we extract a set of columns that guarantee to uniquely identify each customer: superkeys
name, SSN, birthday
ID, name, SSN
define key
a key is a field, or combination of fields, in a database table used to retrieve and sort rows in the table based on certain requirements. Keys are defined to speed up access to data and, in many cases, to create links between different tables.
define primary key
a candidate key designed by the database designer. often used to identify tuples in a relation (for example, courseId may be a primary key in a Course table).
a table can only have 1 primary key
primary keys enforce entity integrity
define candidate key
a candidate key is a column, or set of columns, in a table that can uniquely identify any database record without referring to any other data. Each table may have one or more candidate keys. Each can qualify for primary key (though there can only be one primary key).
define foreign key
a foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. it acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them. technopedia
must be very careful with foreign keys. careless deletion or insertion may destroy the relationship between two tables. see list of bad things on technopedia
foreign keys maintain referential integrity, creating an association between two tables
Two conditions must be met if a set of attributes FK is a foreign key in a relation R that references relation T if it satisfies the following 2 rules
- the attributes in FK have the same domain as the primary key in T
- a nonnull value on FK in R must match a primary key value in T
what is the difference between a candidate key and a primary key
candidate keys can qualify to be a primary key. but the database designer should choose the best one to make a primary key.
maybe we have produceId, productNumber, and productName. which should be the primary?
tips:
nothing that can be null
unique, does not repeat
stable, does not change
ProductID is best
What do all relational database systems support? where is there some variation? give example
primary key constraints
foreign key constraints
do not necessarily support:
domain constraints
microsoft access does not support. so, numberOfCredits range 0-5 cannot be in Access
what enforces integrity constraints?
the database management system, DBMS
what is SQL
Structured Language Query
the language for defining tables and integrity constraints and for accessing and manipulating data
it is a universal language for accessing relational database systems
what do application programs do?
allows users to access a database without directly using SQL, but the applications themselves must use SQL to access the database.
Do all relational database systems support all SQL features?
no. and some systems have their own extensions.
what math provides the theoretical underpining for SQL
relational algebra
tuple relational calculus
**text notes: familiarity with these languages will give you a better understanding of database systems and the SQL language
what is a domain
the set of possible values for a given attribute, and can be considered a constraint on the value of the attribute
what is information
information is an interpretation of data
what do database schemas do
describe the relationships, structures, and constraints of the data in the database
name the three parts of the three-schema architecture for relational databases
- internal schema
- logical schema
- external schema
what is an internal schema
an internal schema describes how data is stored internally in the database
what is a logical schema
presents a logical view of the data
what is an external schema
presents part of the database that is interesting to the users
what is MySQL
Most widely used open source relational database management system that runs a server providing multi-user access to a number of databases
GNU public license
what is LAMP
LAMP is an open source web application software stack
Linux, Apache, MySQL, Perl/PHP/Python
Name some applications that use MySQL
Joomla, WordPress, Drupal, Wikipedia, Facebook, Twitter, YouTube, Flickr
MySQL is a RDBMS, and it doesn’t ship with GUI tools to administer MySQL databases or manage data contained within the database. How do we use it?
We use command line tools
or
we use “front-ends” (desktop software and web applications that create and manage MySQL databases
What is the official front-end tool for MySQL?
MySQL Workbench, developed by Oracle.
How do we access a MySQL database with Java?
JDBC driver for Java
what programming languages is SQL most like
declarative language, some procedural elements
(describes logic of a computation without describing its control flow. minimizes side effects by describing WHAT the program should do, not how. how gets done at program implementation.
HTML is an example. describes what should appear on a webpage. does not specify the possible interactions with it)
