Transcript db-fall2014
Fall 2014, project • Go to https://www.apachefriends.org/index.html • Install a MySQL, Apache, PHP stack • Go to http://localhost/XAMPP • Go to http://localhost/phpmyadmin/ • Note, you can also get stacks at: • The mac app store - MAMP stack • Bitnami.org – WAMP and MAMP XAMPP (or other stack) • Design a database • Create your tables un-normalized • There must be transitive FDs in each original table • There must be at least one pair of MVDs in the original schema (in the same table) • Create at least 10 tables with at least six fields in each table • Normalize your tables into 4NF • Most of your tables should be involved in PK/FK relationships Project requirements • Create your database • Populate your tables with INSERT commands • Write queries that do the following (at least one of each) • • • • • • A simple SELECT FROM WHERE A SELECT FROM WHERE ORDER BY A SELECT FROM WHERE ORDER BY LIMIT A SELECT FROM WHERE with an implied join A SELECT FROM WHERE GROUP BY A SELECT FROM WHERE GROUP BY HAVING Using PHPMyAdmin • A SELECT FROM WHERE with two implied joins, a MAX function, an AVG function, and at least two levels of parentheses embedding in the where clause (i.e., a very nontrivial where clause) • A SELECT FROM WHERE with a NOT operator and an IN operator, and a nested query • A SET command and a nontrivial WHERE clause • An UPDATE with a nontrivial WHERE clause • A CREATE USER • A DROP USER • A START TRANSACTION and a ROLLBACK Using PHPMyAdim, continued • You will run your queries manually for the grader • All queries must compile and run properly – the grader will be there on the day of the final for demos • You will send to the grader via email your pre-normalized schema and your schema in 4NF, as well as show them to him on the day of the final • You will also send the grader the text of all your SQL queries (including the ones making tables and doing updates/inserts). For ugrads & grad students • Create a web page interface to your set of queries so that queries can be chosen from a menu and then run • The interface will show the growing schema as tables are created • For read only queries: The web page interface will display the result of each query, including table names, attribute names, and the tuples returned • For queries that update tables: The interface will show all updated tuples For grad students (or extra credit for ugrads)