Apache Web Hosting, Affordable, PHP5, Comcast - Linux Blog

ENUM and SET, avoiding 46, 47 event table 66 gender table 68 improoving 44 multilingual application 48 organization_category table 72 organization table 72 parameters table 72 person_category table 69 person table 68 road_test table 73 Row Statistics section 62 sale_financing table 71 sale table 70 salutation table 69 scalability over time 44 SET 46 survey_answer table 74 survey_question table 74 survey table 73 tax_rate table 71 validating 48 vehicle_category table 67 vehicle_event table 67 vehicle table 67 too wide table about 12 example 13 example, phpMyAdmin, used 14 example, problem, examining 15, 16 need for 13 script, for creating example 14 U unique key about 40 V views about 53 example 53 previleges 53 updating 54 uses 53

[ 93 ] N non-relational table 12 normalization 10 P phpMyAdmin comments, adding to columns in table 51, 52 Display PDF schema 65 Export feature 65 PDF Page feature 63 primary key about 10, 40 need for 40 Q query optimizer 60 R relational model Codd s rules 9 overview 9 S second level data. See free fields technique slave servers 60 storage engines, MySQL about 54 architecture 54 general guideline 55 InnoDB 54, 55 MEMORY 54 MyISAM 54 NDB 55 pluggable storage engine architecture 54 surrogate key 84 system boundaries identifying 17, 18 model flexibility 19 modular development 18 T table layout primary key 40 rules 40 table name, selecting 40, 41 unique key 40 table name selecting 40, 41 tables about 10 code tables, laying out 43 comments, adding to columns 51, 52 data encoding 62 dynamic table format 61 fixed table format 61 foreign key 55 InnoDB clause 55 InnoDB storage engine 57 list of tables 39 lookup tables 41 name, selection 40, 41 ON UPDATE CASCADE clause, used 56, 57 primary key 40 reference tables 41 referencing tables 55 rules for table layout 40 size reduction 62 structure, improoving 44 too wide table 12 unique key 40 table structure brand_color table 66 brand_model table 66 brand table 65 comments, adding to columns in table 51, 52 condition table 69 credit_rate table 70 empty columns 45 ENUM 46 ENUM, advantages 47 ENUM, disadvantages 47

[ 92 ] data dependency 41 data design technique 10 data elements examples 22 subdividing 28, 29 subdividing, example 28, 29 with formatting characters 29 data models challenges 18 Codd s rules 9 flexibility 19 relational model, overview 9 data naming abbreviations 34, 35 about 34 consistency 36 designer s creativity 34 MySQL issues 36 plural form 35 problems 30-32 table name into a column name 36 data redundancy about 41 drawbacks 41 data structure efficiency, improoving 58 indexes 58 document, gathering existing system 20 forms 20 general reading 19 goal 19 E EAV. See free fields technique Entity-Attribute-Value. See free fields technique entity relationship diagram 10 ERD. See entity relationship diagram F foreign key about 55 benefits 55 constraint 56 defining 56 forms 20 free fields technique about 32 drawbacks 33 example 33 I index about 58 creating 58 creating on composite key 58 EXPLAIN keyword 59 query optimizer 60 interviews goal 20 perceptions 21 perceptions, example 21 relevant questions 21 chronological events, relevant questions 22 destinations, relevant questions 22 existing system, relevant questions 21 sources, relevant questions 22 urgency, relevant questions 22 users, finding 21 users, finding issues 21 M master server 60 modular development advantages 18 priorities 18 MySQL BLOB datatype 62 CHAR datatype 61 datatypes 61 datatypes and storage methods 61 InnoDB 55 query optimizer 60 storage engines 54 TEXT datatype 62 VARCHAR datatype 61

Index A airline system, case study about 75 all persons on a flight, sample query 89, 90 boarding pass, sample query 88 code tables 81 composite-key tables 85 data elements 80 document gathering, results 75-77, 79 passenger list, sample query 88, 89 sample queries 87 sample values 80, 84, 85 tables 80, 84, 85 themed tables 82, 84, 85 C case study airline system 75 car dealer 11 final structure 63 case study, car dealership about 11 car dealer 11 data elements 22 general manager, data elements 23 sales contract, data elements 23 salesperson, data elements 23 store assistant, data elements 24 data model, building 11 example of data cleaning 27 goal 17 scope 17, 18 system goals 12 Codd s rules 10 composite key about 42 using 43 compound key 42 D data access policies 51 application level, security 53 as column name 30-32 as results 29, 30 as table name 30-32 data cleaning 27, 28 data design, with case study 11 data structure 10 data structure, changing 32 data structure, normalization 10 dependency 41 encoding 62 previleges 53 redundancy 41 security 53 data, collecting data elements, example 22 document, gathering 19 interviews 20, 22 system boundaries, identifying 17, 18 data access policies about 51 application level, security 53 data responsibility 51 previleges 53 security 53 views 53 data cleaning 27, 28

where flight_crew.flight_id = 34 order by ‘last name’, ‘first name’ The results are sorted by last name and first name; note the “type” column that indicates whether this person is a passenger or a crew member. Summary From the study of a few documents about an airline system, we listed the possible data elements that become columns grouped into tables. We carefully chose the primary key or keys for each table and built relations between these tables, verifying that all potential data elements were included in at least one table.

passenger.passport_info, airline.description, flight.number from reservation inner join passenger on reservation.passenger_id = passenger.id inner join flight on reservation.flight_id = flight.id inner join airline on flight.airline_id = airline.id where reservation.flight_id = 34 order by reservation.seat Currently, this flight is not very popular, and it looks like Peter and Annie will be able to chat together: All Persons on a Flight In the unlikely event of a plane crash, we might need to extract quickly the list of all persons on a flight. The following query does just that: select passenger.last_name as ‘last name’, passenger.first_name as ‘first name’, ‘passenger’ as ‘type’, airline.description, flight.number from reservation inner join passenger on reservation.passenger_id = passenger.id inner join flight on reservation.flight_id = flight.id inner join airline on flight.airline_id = airline.id where reservation.flight_id = 34 union select crew.last_name as ‘last name’, crew.first_name as ‘first name’, ‘crew’ as ‘type’, airline.description, flight.number from flight_crew inner join crew on flight_crew.crew_id = crew.id inner join flight on flight_crew.flight_id = flight.id inner join airline on flight.airline_id = airline.id

Inserting Sample Values We use the sample values described in the above list of tables. Please refer to this book’s support site.. .(http://www.packtpub.com/support) for the code download which contains the tables’ definition and sample values. Boarding Pass A passenger can print his or her boarding pass while at home by using the website’s quick reference for his or her reservation, which is KARTYU in our example. Here is the generated query to retrieve the boarding pass information: select passenger.last_name, passenger.first_name, flight.number, airline.description, flight.departure_moment, flight.departure_gate, flight.boarding_moment, reservation.seat, plane_section.description from reservation inner join passenger on reservation.passenger_id = passenger.id inner join flight on reservation.flight_id = flight.id inner join airline on flight.airline_id = airline.id inner join plane_section on (airline.id = plane_section.airline_id and reservation.section_id = plane_section.id) where reservation.web_site_quick_reference = ‘KARTYU’ Executing this query retrieves these results: Passenger List Here, the airline wants a list of passengers for a specific flight; we use flight_id,. which is the primary key of the flight table, and not the flight ……………number……… because flight numbers are not unique. select reservation.seat, passenger.last_name, passenger.first_name,

Airline System Data Schema Here again we use phpMyAdmin’s PDF schema feature to display the relations between tables and the keys involved. Sample Queries As a complement to the list of tables and the database schema, let’s see our tables in action! We will enter sample values into the tables, and then build some SQL queries in order to pull the needed data.

table: plane_section_seat column name sample value *airline_id 1 *brand_id 1 *model_id 2 *section_id 1 *seat 19A The plane_section_seat table describes which seats are located in a specific section of the aircraft. This is per airline, brand, model, and section, because different airlines could possess the same kind of aircraft but use different seat numbers or have a larger business section than others airlines. Also, in some cases, seats 1A and 1C may exist but 1B may not. Thus, we need this table to hold the complete list of existing seats. table:flight_crew column name sample value *flight_id 34 *crew_id 9 With these sample values, we can deduce that Dan Murray is the pilot for flight 456 of Air-Quebec. Another possible column in this table would be the status of this crew member for this flight: arrived on time, cancelled, or replaced. table: plane_event column name sample value *plane_id 302 *event_id 1 *start_moment 2008-10-01 end_moment 2008-10-31 table: reservation_registered_ bags column name sample value *flight_id 34 *passenger_id 1302 *tag AQ636-84763 Other columns for tag tracking could be added here.

Normally we would not need the section_id in the reservation table, ………since we can refer to it via the seat_id but the seat_id may be unknown at the time of reservation, so seat assignment can be delayed until the boarding pass is issued. Composite-Key Tables These tables have more than one key because some key segments refer to a code or themed table. table: plane_brand_ model column name sample value *brand_id 1 *id 2 description APM-300 Here, the brand_id and a unique id form the primary keys for the plane model. We want to know to which brand this model refers, and still keep integers for the keys instead of using APM-300 as a key value. table: plane_section column name sample value *airline_id 1 *id 1 description economy Each airline can potentially describe the sections of their planes the way they want some are using hospitality instead of economy. table: airline_brand_model_ restriction column name sample value *airline_id 1 *brand_id 1 *model_id 2 max_number_in_cabin_bags 1 max_number_registered_bags 2 max_weight_registered_bags_kg 50