1 / 38

Creating Database Tables

Creating Database Tables. CS 320. Review: Levels of data models. Conceptual: describes WHAT data the system contains Logical: describes HOW the database will be structured, regardless of the DBMS Physical: describes HOW the database will be implemented using a specific DBMS.

roth-guthrie
Download Presentation

Creating Database Tables

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Creating Database Tables CS 320

  2. Review: Levels of data models • Conceptual: describes WHAT data the system contains • Logical: describes HOW the database will be structured, regardless of the DBMS • Physical: describes HOW the database will be implemented using a specific DBMS

  3. Required Software MySQL DBMS MySQL Workbench SQL editor (or another command-line SQL query editor)

  4. Review: Web/Database Architecture • Request for Web page • that requires database data • Request for Web page that • requires database data Network 6. Web page HTML file downloaded to client 5. Web page HTML file containing database data downloaded to client Web server Web browser • Runs a program • that makes a data • request 4. Data response Database server

  5. MySQL Workbench Architecture • SQL command • SQL command Network 4. Response (erroror confirmation) 3. Response (erroror confirmation) Database server Client running Workbench • Workbench uses: • Running commands to create tables (only way) • Testing queries to use in PHP pages (later)

  6. MySQL Database Structure • A MySQL database consists of multiple user accounts • Also called database schemas • MySQL databases in general: • A user account can contains different databases • UWEC MySQL database: • In our configuration, you can't create new databases • All of your tables are in the same database

  7. MySQL Database Structure • UWEC MySQL database: • In our configuration, you can't create new databases • All of your tables are in the same database • Issues? • All tables must have a unique name • For your projects, you'll use a group database account

  8. SQL Command Types • Data Definition Language (DDL) • Used to create and modify database objects • Data Manipulation Language (DML) • Used to insert, update, delete, and view the data in database objects

  9. DDL Commands • Used to create and modify database objects • CREATE • ALTER • DROP • DDL commands execute as soon as they are issued, and do not need to be explicitly saved

  10. Creating a New Database CREATE DATABASE database_name - General syntax CREATE DATABASE candy - Specific example **You won't do this with the UWEC MySQL database

  11. Specifying Which Database to Use For Creating Tables USE database_name USE candy You'll need to specify to USE your database: USE morrisjp;

  12. Creating a Database Table CREATE TABLE table_name (field1_name datatype size, field2_name datatype size, …) CREATE TABLE candy_product (prod_id BIGINT, prod_desc VARCHAR(30), prod_cost DECIMAL(5,2), prod_price DECIMAL(5,2))

  13. General Notes on SQL Commands Not case-sensitive Can span multiple lines in a text editor To run multiple statements in the editor window, separate each statement with ; To "comment out" (not execute) a statement, enclose it in /* … */

  14. Naming Tables and Fields • Database table and field name rules • 1 to 64 characters long • Can contain letters, numbers, or underscores • Can't contain certain characters (/ \ ,) or characters not permitted in file names • Every table in your database must have a unique name • Every field in a table must have a unique name

  15. Good* Table Naming Practices *Required in this class • Name every table using a combination of 2 words, separated by an underscore • This approach avoids using reserved words • Use the same descriptive first word for all tables in a related database

  16. Good* Field Naming Practices *Required in this class • Name every field using a combination of 2 words, with the first word indicating the table name • Avoids creating homonyms • What about foreign keys? • Use the name that is in the parent table

  17. Database Field Data Types • Specify: • Type of data the field stores • Maximum data size • Purpose: • Specify internal encoding • Optimize internal storage use • Provide error checking

  18. Main Data Types • Text data types • CHAR • VARCHAR • Number data types • Integers: INT and variations • Floating point numbers: DECIMAL, FLOAT • Dates & times • DATE, TIME, and variations

  19. CHAR Data Type • For fixed-width character fields (1-255 characters) • Fields in which you know the exact number of characters • Pads out the remaining characters with blank spaces • Removes the trailing blank spaces when data is retrieved fieldname CHAR(size) cust_type CHAR(1)

  20. VARCHAR Data Type fieldname VARCHAR(maxsize) cust_name VARCHAR(30) • For variable-width character fields (1-255 characters) • Number of characters varies for individual data values • Values are not padded with blank spaces

  21. MySQL Number Data Types: Integers • TINYINT • Signed: -128 to 127 • Unsigned: 0 to 255 • SMALLINT • Signed: -32768 to 32767 • Unsigned: 0 to 65535 • INT • Signed: -2147483648 to 2147483647 • Unsigned: 0 to 4294967295 • BIGINT • Signed: -9223372036854775808 to 9223372036854775807 • Unsigned: 0 to 18446744073709551615

  22. Integer Data Type Usage Choose the "smallest" integer type (SMALLINT, INT, BIGINT) that is big enough to hold the largest possible value Question: Why not just always use BIGINT?

  23. Integer Data Type Examples fieldnameinteger_data_type prod_id TINYINT cust_id INT purch_id BIGINT

  24. MySQL Numeric Data Types: Floating Point Numbers • For values that have a decimal portion • DOUBLE or FLOAT: very large or very small values that have an indeterminate number of decimal places • DOUBLE: takes more space, but is extremely accurate (15 decimal places) • FLOAT: takes less space, use when extreme accuracy isn't required • DECIMAL: for values that have a predetermined number of decimal places

  25. Floating Point Examples fieldname FLOAT fieldname DOUBLE fieldname DECIMAL(precision, scale) pounds FLOAT prod_cost DECIMAL(5,2) Why not just omit the precision and scale? It defaults to the Maximum allowed by your hardware…

  26. Think critically: • Why not just declare all numeric values using the DOUBLE data type? That will take care of all cases. • This doesn't make use of error checking • This doesn't optimize use of data storage capacity

  27. Character and Numeric Data Type Usage • Numeric • Surrogate keys (primary key ID values) • All numeric data you might use in a calculation (prices, quantities, etc.) • Character: Use an appropriate character data type for all fields that are not numbers or dates • Names, addresses, etc. • Postal codes, telephone numbers, social security numbers

  28. MySQL Date and Time Data Types • DATE • Dates ranging from 1000-01-01 to 9999-12-31 • Display in the format YYYY-MM-DD • TIME • Times ranging from is -838:59:59 to 838:59:59 • Can also be used to represent elapsed time intervals • Display in the format HH:MI:SS

  29. MySQL Date and Time Data Types (continued) • DATETIME • Used to store both a date and time component ranging from 1000-01-01 00:00:00 to 9999-12-31 23:59:59 • Displays in the format YYYY-MM-DD HH:MI:SS • YEAR • Can store either a 2-digit or 4-digit year • 4-digit range: 1901 to 2155 • 2-digit range: 70 to 69 (1970 – 2069)

  30. Review: Creating a Database Table CREATE TABLE table_name (field1_name datatype size, field2_name datatype size, …) CREATE TABLE candy_purchase( purch_id INT, prod_id INT, cust_id INT, purch_date DATE, purch_delivery_date DATE, purch_pounds FLOAT, purch_status VARCHAR(10))

  31. Scripts • Text files that contain a series of SQL commands • Each command separated by ; • Purpose: • Write a series of commands and then execute them all at once • Create all tables in a database • Insert a set of test records into the tables • Issue with a script that creates all of the tables in a database: • You can't create two tables with the same name in the same database • In a script file, you always need to (try to) DROP all tables first before you create them

  32. Dropping a Database Table DROP TABLE table_name DROP TABLE IF EXISTS table_name • Use IF EXISTS in a script to avoid error messages

  33. Example DROP TABLE Commands in a Script /*commands to drop all tables */ DROP TABLE IF EXISTS candy_purchase; DROP TABLE IF EXISTS candy_customer; DROP TABLE IF EXISTS candy_cust_type; DROP TABLE IF EXISTS candy_product; /*commands to create tables */ CREATE TABLE candy_cust_type (…

  34. Creating Tables in the MySQL Visual Environment Generates the SQL command to create the table Doesn't save the command or generate a script

  35. Test Yourself: What data type declaration would you use to create a database field that stores part descriptions (DESCRIPTION) in the Premiere Products PART table? VARCHAR(30) CHAR(30) VARCHAR(10) CHAR(10) None of the above

  36. Test Yourself: What data type would you use to create a database field that stores part quantities on hand (ON_HAND) in the Premiere Products PARTS table? INT SMALLINT BIGINT DECIMAL Either a or b

  37. Test Yourself: What data type declaration would you use to create a database field that stores part prices (PRICE) in the Premiere Products PARTS table? VARCHAR(10) DECIMAL(4,2) DECIMAL(5,2) DOUBLE None of the above

  38. Test Yourself: What data type would you use to store ZIP code (ZIP) values in the Premiere Products REP table? CHAR(5) TINYINT DECIMAL(5,0) VARCHAR(10) None of the above

More Related