Cardiff’s School of Computer Science & Informatics
CMT302 “E-Commerce & Innovation” SPRING – LAB 2
Modern EC Development: databases and MySQL Resources and documentation:
– MySQL in the School of Computer Science & Informatics:
Copyright By PowCoder代写 加微信 powcoder
– https://docs.cs.cf.ac.uk/notes/mysql-in-the-school/
– Accessing MySQL Databases from Linux:
– https://docs.cs.cf.ac.uk/notes/accessing-mysql-from-linux/
– Administering MySQL Databases with phpMyAdmin:
– https://docs.cs.cf.ac.uk/notes/administering-mysql-with-phpmyadmin/
– To create a database on COMSC servers, change your password, and other database
management tasks:
– https://dbmanager.cs.cf.ac.uk/mysql/
– SQL Quick Reference From W3Schools:
– https://www.w3schools.com/sql/sql_quickref.asp INSTRUCTIONS:
Attempt as many exercises as you can. If you do not manage to finish all the exercises in the lab, please continue doing them in the next lab or at home. If you find the first few exercises too easy — skip to the harder ones. Remember, we are here to help. If you get stuck – don’t be shy, raise your hand and ask for advice. It is also OK to discuss the solutions with your peers (these labs are not assessed!), however make sure you understand everything by yourself.
Use the separate handout ([CMT302] LAB 2 – mysql – REFERENCE.pdf) to help you complete the exercises.
Good luck!
PRELIMINARIES:
– The school has a MySQL server available at csmysql.cs.cf.ac.uk – You can interact with the MySQL server in several ways, using:
– terminal-based SQL command interface to MySQL; – web-based front end phpMyAdmin,
– Or a GUI frontend, e.g. MySQL Workbench
For these exercises, we will be using the first option, i.e. the terminal-based SQL command interface.
1. Create MySQL account:
1.1. Skip this task if you already have MySQL account. Otherwise, create one by by going to: https://dbmanager.cs.cf.ac.uk.
2. Connect to the MySQL server:
mysql -h csmysql.cs.cf.ac.uk -u
You should then see the MySQL monitor prompt (mysql>), for example:
You can now type your SQL commands. Remember to terminate them with a semicolon. Try:
SHOW databases;
You will be presented with a list of available databases. (You should have permissions to modify the databases that belong to you.)
3. Create a new database called myfirstdb and make it active:
3.1. CreateatableStaffinthedatabasemyfirstdb
See: https://docs.cs.cf.ac.uk/notes/accessing-mysql-from-linux/ for more information
on this command and its switches.
CREATE SCHEMA myfirstdb;
USE myfirstdb;
Verify that the table has been created (albeit empty at the moment) and examine it:
Make sure you understand what the above statements do:
– when creating a table, you need to consider which data types the attributes should have. For instance, in the above table we chose to store first names as strings of variable length up to 30 characters (i.e. varchar(30)), and salaries as integer numbers (i.e. int).
– Explore http://www.w3schools.com/sql/sql_datatypes_general.asp to find out more about the available data types. Further, when creating a table you may want to specify additional options and constraints for your attributes. Use the documentation at http://www.w3schools.com/sql/sql_constraints.asp to find out:
(a) How to make sure that the firstName attribute always has a value (i.e. to disallow NULL values).
(b) How to set the default value for salaries to be 10,000.
3.2. Delete table and verify that it has indeed been deleted. Then create it again.
3.3. You should always define a primary key for your tables. In some cases, one of the existing attributes of the data that you are modelling may serve as the natural primary key (for example, unique student number), in other cases you may need to create a surrogate key just for the purposes of uniquely identifying the rows. Primary key may even consist of multiple attributes. Read http:// www.w3schools.com/sql/sql_primarykey.asp to find out about the syntax for the primary key constraint.
For the sake of example, let’s use staffNo as the primary key in our table Staff. We can declare it so:
ALTER TABLE Staff ADD primary key (staffNo);
(Or alternatively you could have done it when creating the table.) Inspect your table now with: DESCRIBE Staff;
What has changed?
You can use the ALTER command to alter other properties of your tables after it has been created. For example, to make sure that the salaries are never unspecified and the default value is 10,000:
ALTER TABLE Staff MODIFY salary int NOT NULL default 10000;
Inspect your table again. What has changed this time?
3.4. Populatethetablewithdata,byexecutingthefollowingSQLstatement(youcan copy and paste all the statements into cmd and hit enter):
3.5. You can also populate tables from files, such as .CSV. Let’s populate the table with some data using this method. Download the file staff.csv from Learning Central and place it in your current folder, i.e the folder where you were when you started mysql. The following ’scary’ command 1 will import this CSV file into our database table:
Verify that the command worked, by inspecting the contents of the table.
INSERT INTO Staff VALUES
(‘SG1′,’Charles’,’Dickens’,’Assistant’,’18000′,’B003′);
LOAD DATA local infile ’staff.csv’ into TABLE Staff FIELDS
terminated by ’,’ ENCLOSED BY ’”’;
NOTE: If, when loading data from the CSV file, you got this error:
it means the security settings are too strict and loading data from local files is not allowed. To remedy this, restart mysql with the following options:
(Alternatively, you can import the data using phpMyAdmin: select your database and table, and then use the Import feature from the main menu. For the Format select CSV. See the optional Task 6.)
mysql -u username -h csmysql.cs.cf.ac.uk -p –local-infile
3.6. Similarly,usingSQLcommands,createatablecalledBranchusingthe appropriate data types for the attributes (and do not forget to define a primary key). Populate this table from CSV file branch.csv.
When querying the table using SELECT * command, your table should look like this:
1 See https://dev.mysql.com/doc/refman/5.7/en/loading-tables.html 4 of 6
3.7. WriteanSQLcommandtoaddanotherbranchtotableBranch,withnumber B008 located at 12 Millbank, London.
Examine your table.
3.8. AssumingstaffNowasdeclaredasprimarykeyintheStafftable,whatwould happen if you tried to add another record with the value of staffNo that already existed in the table?
3.9. Forexample,trythethefollowingquery(checkandconfirmthatSL21already exists before you run the query):
What is the result of the above query?
3.10. Write an SQL command to modify2 the address of the branch B008 to be PO Box
3255, London.
Write SQL queries to answer the following questions (or carry out operations).
(a) In which branch does work?
(b) Which employees work in branch B003?
(c) Which branches are located in London?
(d) What are the names of employees who have salary greater than 20, 000?
(e) What is the average salary? The largest? The smallest?
(f) What are the names of employees who have a below average salary? Above average?
(Hint: Use a sub-query to find the average salary first, then filter the employees using the result of this sub-query.)
(g) Produce the list of all employees sorted by salary in ascending order. Do the same in descending order.
(h) Who are the three lowest paid employees?
(Hint: use data selection limit , e.g. see: http://www.w3schools.com/php/ php_mysql_select_limit.asp.)
(i) Determine the set of all job titles (positions). That is, find the list of all job titles without duplication.
(Hint: http://www.w3schools.com/sql/sql_distinct.asp.)
2 Hint: see See http://www.w3schools.com/sql/sql_update.asp 5 of 6
(j) What are the names and salaries of employees who work in London?
(Hint: since the information about names and salaries is in table Staff while the information about cities is in table Branch, you need to do a join.
See http://www.w3schools.com/sql/sql_join.asp.)
(k) Are there any assistants working in Aberdeen?
(l) Employees in which city have the highest salary?
(m) What is the top salary in Glasgow?
(n) Which branches do not have a manager?
(o) Which branches do not have any employees?
(p) Find all last names ending with an “e”.
(q) Reward Susan Brand who works in B003 as a manager by raising her salary to 36, 000.
(r) In fact, let’s reward all managers: raise their salaries by 10%.
(s) Branch B007 is closing. Remove all employees working there, and remove the branch itself.
(t) Hire a new employee. Her name is and she will be working as an assistant in the Glasgow branch with the starting salary of 22, 000.
5. Create a database suitable for a typical e-commerce system
For this exercise, you can use the database design/model created in our workshop, or a different design suitable for an EC system.
Implement the database, using MySQL commands practiced earlier.
6. [Optional]
Get familiar with alternative way of administering databases, using:
– MySQL Workbench (see quick start guide here: https://docs.cs.cf.ac.uk/notes/accessing- mysql-from-linux/
– and/ or web-based phpMyAdmin: https://docs.cs.cf.ac.uk/notes/administering-mysql-with- phpmyadmin/
Enjoy 😊 ! 6 of 6
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com