CST363 Week 4

Been very busy with work and have fallen behind in class (not good). This week we learned about database normalization. When I learned about databases I just took these items for granted, and since I have not had to work with large databases it has not made a difference. This was more about why we make the decisions in database design.

Best Sci-Fi Movies to re-watch:
15) Sphere
14) Titan AE
13) Enemy Mine
12) Pitch Black
11) Stalker (79)
10) 2010 - The Year We Make Contact
09) Primer
08) Heavy Metal
07) Mr Nobody
06) Silent Running
05) Equilibrium
04) Strange Days
03) eXistenZ
02) Sunshine
01) Moon

CST363 Week 3

This week we covered the basics of SQL.

SQL for creation of tables:

set echo on

DROP TABLE truck;
DROP TABLE type;
DROP TABLE base;
CREATE TABLE base (
 base_code char(3) PRIMARY KEY,
 base_city varchar2(15),
 base_state char(2),
 base_area_code char(3),
 base_phone varchar2(10),
 base_manager varchar2(20)
);
INSERT INTO base VALUES('501', 'Mulfreesboro', 'TN', '615', '123-4567', 'Andrea D Gallager');
INSERT INTO base VALUES('502', 'Lexington', 'KY', '568', '234-5678', 'George H. Delarosa');
INSERT INTO base VALUES('503', 'Cape Girardeau', 'MO', '456', '345-6789', 'Maria J. Talindo');
INSERT INTO base VALUES('504', 'Dalton', 'GA', '901', '456-7890', 'Peter F. McAvee');
CREATE TABLE type (
 type_code char(1) PRIMARY KEY,
 type_description varchar2(30)
);
INSERT INTO type VALUES('1', 'Single box, double-axle');
INSERT INTO type VALUES('2', 'Single box, single-axle');
INSERT INTO type VALUES('3', 'Tandem trailer, single-axle');
CREATE TABLE truck (
 truck_num char(4) PRIMARY KEY,
 base_code char(3),
 type_code char(1),
 truck_miles number(7,1),
 truck_buy_date date,
 truck_serial_num varchar2(20),
 CONSTRAINT fk_base_code FOREIGN KEY (base_code) REFERENCES base(base_code),
 CONSTRAINT fk_type_code FOREIGN KEY (type_code) REFERENCES type(type_code)
);
INSERT INTO truck VALUES('1001', '501', '1', 32123.5, '23-Sep-2007', 'AA-322-12212-W11');
INSERT INTO truck VALUES('1002', '502', '1', 76984.3, '05-Feb-2006', 'AC-342-22134-Q23');
INSERT INTO truck VALUES('1003', '501', '2', 12346.6, '11-Nov-2006', 'AC-445-78656-Z99');
INSERT INTO truck VALUES('1004', null, '1', 2894.3, '06-Jan-2007', 'WQ-112-23144-T34');
INSERT INTO truck VALUES('1005', '503', '2', 45673.1, '01-Mar-2006', 'FR-998-32245-W12');
INSERT INTO truck VALUES('1006', '501', '2', 193245.7, '15-Jul-2003', 'AD-456-00845-R45');
INSERT INTO truck VALUES('1007', '502', '3', 32012.3, '17-Oct-2004', 'AA-341-96573-Z84');
INSERT INTO truck VALUES('1008', '502', '3', 44213.6, '07-Aug-2005', 'DR-559-22189-D33');
INSERT INTO truck VALUES('1009', '503', '2', 10932.9, '12-Feb-2008', 'DE-887-98456-E94');

CST363 Week 2

During this week we delved into Relational Algebra (a procedural query language, which takes instances of relations as input and yields instances of relations as output. It uses operators to perform queries. An operator can be either unary or binary. They accept relations as their input and yield relations as their output.) and how it relates to SQL (Structured Query Language). We were also introduced to SQL commands and installed a local Oracle database.

CST363 Week 1

Installed Oracle Linux/Database virtual machine.


What is Information Technology? It's the technology that helps the organization collect, store, retrieve and apply knowledge to solve problems; IT converts the raw material of information into usable knowledge. IT refers to all forms of technology applied to processing, storing and transmitting information in electronic form.

CST363 Week 0

We covered the concepts behind database design, in particular the various keys. I have always had a problem understanding the conceptual keys, but I did find a helpful video at https://youtu.be/dYNw9Va7AtM.

• A Super key is any combination of fields within a table that uniquely identifies each record within that table.
• A Candidate key is a subset of a super key. A candidate key is a single field or the least combination of fields that uniquely identifies each record in the table. The least combination of fields distinguishes a candidate key from a super key. Every table must have at least one candidate key but at the same time can have several.
• A Primary key is a candidate key that is most appropriate to be the main reference key for the table. As its name suggests, it is the primary key of reference for the table and is used throughout the database to help establish relationships with other tables. As with any candidate key the primary key must contain unique values, must never be null and uniquely identify each record in the table.
• A Foreign key is generally a primary key from one table that appears as a field in another where the first table has a relationship to the second. In other words, if we had a table A with a primary key X that linked to a table B where X was a field in B, then X would be a foreign key in B.
• A table may have one or more choices for the primary key. Collectively these are known as candidate keys. One is selected as the primary key. Those not selected are known as Secondary keys or alternative keys.
• A Compound key consists of more than one field to uniquely identify a record. A compound key is distinguished from a composite key because each field, which makes up the primary key, is also a simple key in its own right.
• A Composite key consists of more than one field to uniquely identify a record. This differs from a compound key in that one or more of the attributes, which make up the key, are not simple keys in their own right.

If that makes your brain hurt, don't worry, it makes mine hurt as well. Luckily practice makes this easier to understand than just explaining it.

Continued on Week 1