CREATING AND POPULATING THE DATABASE
Q1. One of the most annoying things
about a Graphical User Interfaces (GUI) based database management systems such
as Microsoft Access is that it’s never easy to remember what you clicked and in
what order. So it’s impossible to maintain complex databases with a GUI based
tool such as MS Access. What can I do about it?
A1. There is something you can do!
Don’t use a GUI to create and populate you database. If you do use a GUI then you will not be able
to maintain the database.
So what can I do?
Use SQL to create, populate and
modify the database and save the SQL script commands in text file.
If you make changes latter then
simply make the changes to the SQL scripted statements and run the SQL stamens
all over again to recreate every thing from scratch.
Q2. Is there a tool that takes a
text file as input of SQL statements and executes them?
A2. Yes. Prof. Devi has written a program that
executes SQL scrip files. It’s free it’s called SQLExec download it . Put it into a a folder and set the PATH to
that folder so that you can execute it from any folder. You execute SQLExec by typing at a command
prompt in a command window (black DOS window). SQLExect filename where the file name is the text file with an
sql extension e.g., myfile.sql, of your scripted SQL commands. If you just type
SQLExec at the DOS prompt then you get instructions on how to use it.
Q3. Does it matter in which
order I write my SQL statements when creating and populating the database?
A3. It does matter!
Q4. In what order do I write my
SQL commands?
A3. First you write the Drop
Table commands to get rid of the tables that you will recreate and repopulate.
Q5. Dos it it matter in which order
I drop the tables?
A5. Yes. As you have seen in the Integrity chapter the
child table must be dropped before the parents table(s). Just as when you create tables the parent
tables come before the children! So dropping tables is done in reverse order
i.e., the children must go before the parents!
Q6. What’s next?
A6. Next you write Create Table
commands.
Q7. In what order do I create my
tables.
A7.If you are writing referential integrity constraints when you create the tables then create the parent tables before the child tables. You could create the tables in any order
provided you write the referential integrity constraintsafter you create all the tables. However the parent table should be populated before the child tables.See Integrity chapter.
Q8. What’s next after creating
the tables?
A8. Write the integrity constraints
for each table. See Integrity
chapter.
Q9. How many integrity constraints
do I write?
A9. You write a Primary Key
constraint for every table. Then you write a referential constraint for every
foreign key. See Integrity chapter.
Q10. What’s next?
A10 You populate your tables
with the insert command. It may be a good idea to write these insert command in
a different file.
Q11. In what order do I populate
my tables?
A11. You populate the parent tables before the children tables.
Q12. Can you give me an example of an SQL script file?
A12. Yes. Here is a simple one.
Here is a more realistic one. and here it is in an Registration System application.