review doing it all over again

15
Review Doing it all over again

Upload: databaseguys

Post on 07-Dec-2014

364 views

Category:

Documents


0 download

DESCRIPTION

 

TRANSCRIPT

Page 1: Review Doing it all over again

Review

Doing it all over again

Page 2: Review Doing it all over again

What We Covered

• Database basics and terminology• Database Design and Normalization• SQL• ADO and XML (briefly)

Page 3: Review Doing it all over again

Database Basics

• A database stores information relevant to some business need

• A Relational database, stores that information in tables that are related to each other by means of certain “Key” fields

• A Relational Database Management System (RDMS) is software that manages relational databases

Page 4: Review Doing it all over again

Some Major RDMSs

• SQL Server (Microsoft)• Oracle • MySql (Open Source)• DB2 (IBM)

Page 5: Review Doing it all over again

Database Design

• The Design process is a long and complex– First you must understand the purpose of the

database– Next gather requirements– Thirdly identity potential attributes (fields) and

entities– Next define the entities and their attributes– Next define the relations between the entities– Finally evaluate the design for normalization

Page 6: Review Doing it all over again

Keys

• Every table should have a Primary Key• A primary key uniquely identifies a row.

It cannot ever repeat in its own table• Tables are related by copying a primary

key from one table into another table. The primary key in the second table is a “Foreign Key.” It can repeat in the second table as many times as needed

Page 7: Review Doing it all over again

Relations

• There are three kinds of relations– One to one (rare)– One to many (what you want 99% of the

time)– Many to Many (Must always be resolved

into a one to many by creating a linking table)

Page 8: Review Doing it all over again

• Course and student have a many to many relationship

• Section becomes the linking table which reduces the many to many to two one to many relations

Course

PK CourseID

Coursename Credits

Student

PK StudentID

StudentLastName

Section

PK,FK1 CourseIDPK,FK2 StudentID

Page 9: Review Doing it all over again

Some Normalization hints

• Never list several items in one field• If you find yourself number columns like

Track1, track2, etc. , you need a new table

• If anything in a table depends on another field in the table that is not the key, you need another table

Page 10: Review Doing it all over again

Building the Database

• Building the Database requires knowing the Specific DBMS you are using and its data types and file systems

Page 11: Review Doing it all over again

SQL

• SQL is the language relational database use for everything they do, including creating database objects, retrieving data, and manipulating data

Page 12: Review Doing it all over again

Basic SQL Examples

• SELECT * FROM Customer• UPDATE Customer SET

Lastname=“Smith” WHERE CustomerID=345

• INSERT INTO Customer(Lastname, Phone) VALUES (“Smith”, “2065456677”)

Page 13: Review Doing it all over again

ADO

• ADO is a set of tools Microsoft created to create applications which connect to databases.

• There are other tools for creating Database applications such as PHP, or Java

Page 14: Review Doing it all over again

XML

• Xml has become the universal language for transporting data between separate applications

• It is operating system, database and application neutral

• Increasingly Databases are incorporating xml as a native datatype

Page 15: Review Doing it all over again

What Next

• A deeper sense of SQL (ITC 222)• ADO Programming (ITC 172, ITC 224)• Web Database with PHP (ITC 280)• Database Administration and

Development with SQL Server 2005 (ITC 224 and ITC 226)