You may work on this assignment as a group of one or two students.
If anything does not work as described here,
notify the instructor immediately.
Do one of the following tasks.
app_skeleton
implemented as described in CS 440 Assignment #5.
See the instructor for the details.
mantis-1.0.6 and zen_cart in
/usr/local/classes/eecs/winter2007/cs440/public_html
on the ENGR file server,
and they can be accessed with the following URLs:
grep -r cs440 * in the top-most directory
of each application under
/usr/local/classes/eecs/winter2007/cs440/public_html.
xxx.tar.gz can be unpacked with
Unix command tar -xvzf xxx.tar.gz.
INSTALL
in directory mantis-1.0.6/doc.
In installing, say, Mantis, create your database with name
cs549_your-login-name_mantis.
install.txt.
ENGINE=INNODB option,
they must be recreated with this option,
since the INNOB engine is needed to support foreign key constraints.
alter table table_name ENGINE=INNODB;You can create a script containing this statement for every table and execute it. I use a keyboard macro of emacs for this kind of editing work. Alternatively, you may use phpMyAdmin to change the engine option for each table. Click the name of a table on the left-hand side, select Operations, and change the engine. Repeat this process for each table within the database.
create table supplier_product (
supplier_product_id int primary key auto_increment,
supplier_id int not null,
product_id int not null,
whole_sale_price integer
FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id),
FOREIGN KEY (product_id) REFERENCES product(product_id)
) ENGINE=INNODB;
Note the ENGINE=INNODB option at the end of
the create table statement.
ALTER TABLE mantis_bug_table
ADD CONSTRAINT mantis_bug_table_bug_text_id_fkey
FOREIGN KEY (bug_text_id) REFERENCES mantis_bug_text_table(id);
ALTER TABLE mantis_bug_table
ADD CONSTRAINT mantis_bug_table_profile_id_fkey
FOREIGN KEY (profile_id) REFERENCES mantis_user_profile_table(id);
. . .
\u information_schema
select * from key_column_usage where position_in_unique_constraint is not null;
\u mmantis
Foreign key constraints can be dropped as follows:
ALTER TABLE mantis_bug_table
DROP FOREIGN KEY mantis_bug_table_bug_text_id_fkey;
ALTER TABLE mantis_bug_table
DROP FOREIGN KEY mantis_bug_table_profile_id_fkey;
. . .
For a PostgreSQL database, change DROP FOREIGN KEY
to DROP CONSTRAINT.
Now, create in directory sql_queries_mysql
two files xxxx_fkeys_create.sql
and xxxx_fkeys_drop.sql
that add and delete foreign key constraints.
zencart_add_drop_fkeys.php in
zen_cart/forms_mysql/utilities/ to create files
xxxx_fkeys_create.sql
and xxxx_fkeys_drop.sql.
The script considers column zzz_id in table yyy
as a foreign key to column zzz_id in table zzz
if such a column exists.
mysql> \u information_schema -- connect to the INFORMATION_SCHEMA database.
mysql> select * from key_column_usage
where POSITION_IN_UNIQUE_CONSTRAINT is not null;
Foreign key constraints for a table can be checked as follows:
mysql> \u information_schema -- connect to the INFORMATION_SCHEMA database. mysql> show table status from database_name like 'supplier_product';
mysql> \u information_schema -- connect to the INFORMATION_SCHEMA database.
mysql> select * from key_column_usage
where POSITION_IN_UNIQUE_CONSTRAINT is NULL;
Start --> Settings --> Control Panel -> Administrative Tools --> Data Sources (ODBC)When a dialog for DSNs is displayed do
User DSN --> PostgreSQL Unicode --> Add --> PostgreSQL UnicodeProvide the name of your database for Database Name,
nagara.een.orst.edu for the server name,
and your login name, and then click the Save button.
Now, you can activate Visio:
Programs --> Microsoft Office --> Microsoft Visio 2003Once the window of Visio is opened, do
File --> New --> Database --> Data Model Diagram (Metric) Database --> Reverse Engineer --> ODBC Generic Driver, PostgreSQL Unicode --> Select Tables --> ...Use the Help pages for further information.