review doing it all over again
DESCRIPTION
TRANSCRIPT
Review
Doing it all over again
What We Covered
• Database basics and terminology• Database Design and Normalization• SQL• ADO and XML (briefly)
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
Some Major RDMSs
• SQL Server (Microsoft)• Oracle • MySql (Open Source)• DB2 (IBM)
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
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
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)
• 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
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
Building the Database
• Building the Database requires knowing the Specific DBMS you are using and its data types and file systems
SQL
• SQL is the language relational database use for everything they do, including creating database objects, retrieving data, and manipulating data
Basic SQL Examples
• SELECT * FROM Customer• UPDATE Customer SET
Lastname=“Smith” WHERE CustomerID=345
• INSERT INTO Customer(Lastname, Phone) VALUES (“Smith”, “2065456677”)
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
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
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)