ER Entity Relationship model SD Forms the basis for designing the database

Er entity relationship model sd forms the basis for

This preview shows page 16 - 34 out of 75 pages.

E/R Entity-Relationship model (SD): Forms the basis for designing the database later on. Formalized methods for describing the mapping from: E/R model to a set of tables. Common way of communicating the structure of a relational database. Bank Account Has 16
Image of page 16
MySQL Workbench Time to get practical 17
Image of page 17
MySQL and MySQL Workbench Workbench: An interface for using MySQL on your computer You also need a local database server Download and install BOTH: MySQL Server: MySQL Workbench:
Image of page 18
-Open MySQL Workbench -Click on + for new connection
Image of page 19
- Type a Connection name (whatever you like) - Log in as root for now (so basically leave everything else as is and click OK)
Image of page 20
- Your new connection is now ready - click it - Type in your chosen root password and click OK
Image of page 21
Your database connection is now ready 1. This is where you write your SQL code 2. This is where you click to run your code
Image of page 22
Make sure your server is running (click on Server Status to check)
Image of page 23
PART 2: Create databases and tables Datatypes 24
Image of page 24
MySQL Basics - Create and insert data 25
Image of page 25
CRUD We are going to work with text and numbers in our databases. As in programming we have operations for: 1. Create content 2. Read content 3. Update content 4. Delete content 26
Image of page 26
CREATE Three levels of CREATE: We can CREATE a database We can CREATE tables inside a database We can INSERT data in a table 27
Image of page 27
CREATE Database Write script in MySQL Workbench and run it: (note that # are comments in SQL code also) # Which databases are created? Shows a list SHOW databases; # Create a new database and switch to it CREATE DATABASE hospital; USE hospital; Once you have created your database, you can create tables inside it 28
Image of page 28
Delete Database (DROP) Once you created a database, you will get an error that it already exists, if you run this script again. If you want to start from scratch, you can delete an existing database: # Create a database, override old (update) DROP DATABASE IF EXISTS hospital; CREATE DATABASE hospital; USE hospital; 29
Image of page 29
CREATE table We will create the tables in the same file, since this is our structure for the database design. Continue in same file/ window in MySQL Workbench: CREATE table PATIENT ( first_name varchar(100), last_name varchar(255), ); 30
Image of page 30
INSERT values Once we have our database and table, let’s insert some data in the table. Create a new file for this: USE hospital; INSERT INTO patient (first_name, last_name) VALUES('Hans', 'Andersen'); #OR …. INSERT INTO patient VALUES('Hans', 'Andersen'); 31
Image of page 31
Read values (SELECT) We can’t see what’s actually in the table, unless we read from it after inserting: SELECT * FROM patient; 32
Image of page 32
All together You can run it all together in one SQL file (enter in workbench and save your file as for example hospital.sql): DROP DATABASE IF EXISTS hospital; CREATE DATABASE hospital; USE hospital; CREATE TABLE patient ( first_name varchar(255), last_name varchar(100) ); INSERT INTO patient VALUES(’Hans’, 'Andersen'); SELECT * FROM patient; 33 Result of select
Image of page 33
Image of page 34

You've reached the end of your free preview.

Want to read all 75 pages?

  • Spring '16
  • feofje
  • Relational model

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

Stuck? We have tutors online 24/7 who can help you get unstuck.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes