is this in 3NF?

Question

is this in 3NF?

mysql show tables;
+--------------------+
| Tables_in_hospital |
+--------------------+
| address            |
| bed                |
| department         |
| hospital_number    |
| phone_number       |
| region             |
| type               |
+--------------------+

mysql desc hospital_number;  //PRI key = Hospital_Number

+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| name            | varchar(64) | NO   |     | NULL    |       |
| Hospital_Number | int(11)     | NO   | PRI | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

mysql desc address; //PRI key = Address, FOR key = Hospital_number

+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| Address         | varchar(128) | NO   | PRI | NULL    |       |
| Latitude        | double       | NO   |     | NULL    |       |
| Longitude       | double       | NO   |     | NULL    |       |
| Postcode        | varchar(32)  | NO   |     | NULL    |       |
| Hospital_Number | int(11)      | NO   | MUL | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+

mysql desc department; //PRI key = Hospital_number + Department, 
                        //FOR key = Hospital_number

+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| Hospital_Number | int(11)     | NO   | PRI | NULL    |       |
| Department      | varchar(64) | NO   | PRI | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

mysql desc bed; //PRI key = Hospital_number + Bed, 
                 //FOR key = Hospital_number
+-----------------+---------+------+-----+---------+-------+
| Field           | Type    | Null | Key | Default | Extra |
+-----------------+---------+------+-----+---------+-------+
| Hospital_Number | int(11) | NO   | PRI | NULL    |       |
| Bed             | int(11) | NO   | PRI | NULL    |       |
+-----------------+---------+------+-----+---------+-------+

mysql desc phone_number; //PRI key = Hospital_number + Phone_number, 
                          //FOR key = Hospital_number
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| Hospital_Number | int(11)     | NO   | PRI | NULL    |       |
| Phone_Number    | varchar(64) | NO   | PRI | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

mysql desc region; //PRI key = Hospital_number + region, 
                    //FOR key = Hospital_number
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| Hospital_Number | int(11)     | NO   | PRI | NULL    |       |
| Region          | varchar(12) | NO   | PRI | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

mysql desc type; //PRI key = Hospital_number + type, 
                  //FOR key = Hospital_number
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| Hospital_Number | int(11)     | NO   | PRI | NULL    |       |
| Type            | varchar(12) | NO   | PRI | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

I made a database table using MySQL for the first time, and I don't know if this satisfies 3NF. 1 NF was satisfied by separating the department table from the hospital table. I think 2 NF, 3 NF was satisfactory, but I'm not sure. Please check my Databases What is wrong part of my Tables?

Answer 1

What I said about having a many-to-many relationship between hospitals and regions (or types) is an over complication: a given hospital is not likely to be associated with multiple regions (at least not in your model; hospitals can have multi-region presence). So we will go with your simpler one-to-many relationship. The problem in your model still exists whereby you may have multiple hospitals in the same region and if you decide to change the name of the region, you will be required to update multiple rows (same story with the type table):

Using tables hospital_number (which, by the way, I probably would have just named hospital) and region as an example (you can generalize this to table type):

CREATE TABLE `region` (
  `Region_Number` int(11) NOT NULL AUTO_INCREMENT,
  `Region` varchar(12) NOT NULL,
  PRIMARY KEY (`Region_Number`)
) ENGINE=InnoDB;


CREATE TABLE `hospital_number` (
  `name` varchar(64) NOT NULL,
  `Hospital_Number` int(11) NOT NULL AUTO_INCREMENT,
  `Region_Number` int(11) NOT NULL,
  PRIMARY KEY (`Hospital_Number`),
  CONSTRAINT FOREIGN KEY (`Region_Number`) REFERENCES `region` (`Region_Number`) ON UPDATE CASCADE
) ENGINE=InnoDB;

Note that in table region, column Region is not part of the primary key.