Please send questions to
st10@humboldt.edu .
Some more database fundamentals and a few comments on database security
note:
because databases have "grown" from the organization/mainframe world,
the relational algebra world, and people just used to tables,
...sometimes you'll see different terms for (essentially) the same thing:
tables, columns, rows <--- being used to tables
relations, attributes, tuples <--- from relational algebra
files (yuck!), fields, records <--- from mainframe "world"
Four main elements of a database
1. user data
2. metadata
3. indexes
4. application metadata
1. user data --
* the most obvious of the elements in a database...!
* in a relational database, the user data is represented in the
form of relations
(precise definition of a relation to come next week...)
* we will use several "formats" for depicting these relations,
and so you should be familiar with them:
1. TABULAR FORM
...depict the relation as a table, with rows and columns...
student:
student_num student_lname student_phone advisor_lname
----------- ------------- ------------- -------------
100 Jones 123-4567 Smith
101 Nguyen 234-5678 Silva
102 Garza 345-6789 Schmidt
benefits:
* straightforward;
* can see contents;
* can see basic relation structure
drawbacks:
* cannot usually tell what the relation's primary key is;
* cannot see the domain of each column (the set of possible
values for a column)
* cannot see precisely how different relations relate to
each other
* can be a pain to type
2. RELATION STRUCTURE form
* mostly shows a relation's structure -- not its contents,
not its columns' domains, either;
* short, easier to type;
table_name(PRIM_KEY_COL1, PRIM_KEY_COL2, ..., col_name3, col_name4)
foreign key (col_name3) references other_table(col_name_something)
student(STUDENT_NUM, student_lname, student_phone, advisor_name)
benefits:
* still pretty straightforward;
* nice and concise; easy to type;
* can see the basic relation structure;
* can see the relation's primary key and foreign keys (if any)
drawbacks:
* still can't see the domain information (not even implied)
* can't see the contents
3. CREATE-TABLE-STATEMENT form
...write a relation in the form of a SQL create-table statement:
create table student
(student_num char(3),
student_lname varchar2(30),
student_phone char(8),
advisor_lanem varchar2(30),
primary key (student_num)
);
* little less straightforward than the other two forms --
* but less typing than tabular form,
* and includes some physical domain information about the columns;
* still have the relation structure
* includes the primary key and foreign keys (if any)
* still can't see the contents
* can see how relations relate to each other
ASIDE: "good" relation structure vs. "poor" relation structure:
(would better vs. worse be more appropriate? 8-) )
* for a given scenario, not all relation structures are created equal!
...some will work better in the scenario, more naturally, more
smoothly;
...some will let users express data the way they expect to;
...some will increase the long-term data integrity of the data in
the database;
...some will make it easier to build robust applications atop that
structure;
* consider these two relation designs:
(not so good -- unnecessary data duplication, too big a chance of
messing up the advisors' details over time, etc.)
----------------
Student1(STUDENT_NUM, stu_lname, stu_phone, advisor_num, advisor_lname,
advisor_phone)
vs.
(better, at the cost of some necessary data duplication - easier
to query in different ways, easier to maintain data integrity)
----------------
Student2(STUDENT_NUM, stu_lname, stu_phone, advisor_num)
foreign key (advisor_num) references advisor(advisor_num)
Advisor(ADVISOR_NUM, advisor_lname, advisor_phone)
2. metadata - data about the data
the description of the structure of the database;
it is what makes the database self-describing
* sometimes this is called the data dictionary;
* in a relational database, these are often in the form of
relations, sometimes called system tables;
* Oracle does this -- it maintains certain data dictionary
tables --
user_tables (for example),
(and user_tab_columns, user_views, user_objects, user_catalog, ...)
3. indexes - overhead data SUCH AS indexes
* a physical index improves the performance and accessibility
of QUERIES of the data (sometimes at a cost during inserts,
updates, and deletes...)
...to make certain queries faster;
"should be reserved for cases where they are truly needed"
4. application metadata -
* used to store the structure and format of user forms, reports,
queries and other application components supported by that DBMS
NOW -- let's discuss 3 of those common capabilities a DBMS must generally
provide a bit more:
...that a DBMS is expected provide (amongst other things):
DDL - data definition language
DML - data manipulation language
DCL - data control language
DDL - lets users define their database using the DBMS!
in SQL, create table statement lets you create a table;
(and drop table lets you destroy a table...)
also:
ALTER - alter the structure of a database
(and others)
DML - lets users insert, update, delete, and query data
in SQL, we have statements such as:
SELECT - to query data
INSERT - to insert new rows
DELETE - to delete existing rows
UPDATE - to modify existing rows
(and others)
DCL - help to control access to the data -- data control language
in SQL, we have statements such as:
GRANT - to give a user access privileges to some table
REVOKE - to remove privileges from a user for some table
Can you see that DCL may be one useful component of data security,
trying to protect/safeguard your database data?
In SQL, (not going into ALL the things a DBA - database administrator
- can do!) -- amongst the things you can do is to
GRANT select and/or insert and/or update and/or delete access
to different users to a table;
...based on the access appropriate for that user for that
data;
In Oracle, your tables you create really have a "more full" name than
that you give in the create table statement --
each, really, is also preceded by <username>.
so, when I created table parts last week,
really, its fuller name was st10.parts
THAT SAID... let's try a GRANT experiment!
grant syntax:
grant <privilege_list>
on <object_name>
to <user_list>;
<privilege_list> -- comma-separated list of the desired access levels
select, insert, update, delete
<object_name> -- table name, for example
<user_list> -- a comma-separated list of user names...
revoke syntax:
revoke <privilege_list>
on <object_name>
from <user_list>;