Loading

Alison's New App is now available on iOS and Android! Download Now

Study Reminders
Support
Text Version

Set your study reminders

We will email you at these times to remind you to study.
  • Monday

    -

    7am

    +

    Tuesday

    -

    7am

    +

    Wednesday

    -

    7am

    +

    Thursday

    -

    7am

    +

    Friday

    -

    7am

    +

    Saturday

    -

    7am

    +

    Sunday

    -

    7am

    +

Welcome to week 9 of Introduction to modern application development. This week, we willstart assembling all the pieces that we have learned up to the last time, before which we learnabout MySQL. MySQL is a database management system(relational ). We will take adesigners perspective while learning about databases.(Refer Slide Time: 01:07)
A database is a collection of tables.In essence a table is no different from a file, with thee following distinction:1. a file is just a collection of bytes, while a database is analogous to a directory andtables are analogous to files.2. files can contain just any odd byte that you want to put in, tables have structure, theyare collections of records.3. Just like files are made of lines; lines are made of words, databases are made ofstructures (example: ”struct” in C) with attributes/member variables and their valueswhich get stored in an easily accessible fashion.4. An odd thing about databases is the language that is used to access them. The querylanguage is a way of operating on the entire set of records at the same time rather thanone record at a time. Though updates often go to a single record but retrievals areusually in bulk.
We will only cover those parts of that language that are needed in this course. It is far too biga topic to address in any useful fashion.
(Refer Slide Time: 03:07)
Design tables and records: Entity Relationship DiagramMany information processing problems can be thought of as made of two kinds of things:1. Entities which roughly correspond to nouns.
2. Relationships which correspond to verbs, in ordinary English.To build a fairshare like application:1. Entities are usually people, the group of friends, etc and the attribute is their names.2. The second entity is their expenses; the amounts that they spend and so an expensehas two primary features that distinguish it, which is when did it happen and for whatamount did it happen.3. LINK BETWEEN THE ENTITIES: The action of spending. A person is related to aparticular expense because “that person spends a certain amount of money on acertain date”.
NOTE:● This above link/ relationship is not what is called a relationship in a database, it is oneof the odd terminological things one has to get over with.● Entities are represented as tables, though sometimes tables can also representrelationships.(Refer Slide Time: 05:18)
Database : nptelTables:Users : The users of a fairshare like application (i.e.an entity)Expenses: Tracks monthly expenditure.
Attributes.user ID : attribute of a user entity; unique to each user.Ctime : the time at which a user was created.Etime: the time at which the expense was created.Uname : the name of the userAmount : The cost.
Observe the type declaration used for attributes in the above figure.The nptel database is analogous to a directory, “users” is a file and “expense” is a file. Thecontents of users are basically values of “usrId, Ctime, Uname” and the contents of theexpenses table are values,”usrID, Etime and Amount”.(Refer Slide Time: 06:58)
Querying a database:1. register new user,2. record an expense3. ask for a report the way we had planned.
By default, MySQL has a login feature. Users can share access with other users. A similarstructure exists inside a database where a database can have certain users and each user, candecide who can access which database, who can access which table, what they can do withtheir table and so on. So by default, MySQL (here, MySQL in xampp) has a user called root
and there is no password for that user (by default), which is pretty dangerous in realdatabases.The root is the all powerful user, one needs to create a strong password for the root.Since this is just a demo, we are not setting a password for root.● Login as root: “mysql -u root -p”● Commands for looking at databases and tables: “show databases” for displaying thenames of all the databases created; “create database nptel3” or creating new databaseswith name of database nptel3;”create user ‘n1’@’localhost’ identified by p1”, createsa user n1 with password p1.● Where did this creation of the user go? Users are created and stored in the databasewhich itself is called MySQL. (So user information querying commands will besimilar to database querying commands).● To look at any database: “use dbname” command( replace dbname with name ofdatabase, say, use nptel3)● In a database, command “show tables” displays tables.
The database we actually installed in xampp is an open source version of MySQL whichOracle bought. The new open source version is called MariaDB. Nonetheless, you should justthink of it as MySQL. So, users are created inside a database which is itself called MySQLandWhat are tables made of?● A table is described by a schema which is like a class or a record.● For describing the schema of a table tbname:“describe tbname”● Output: Fields, type of each field, can the field be null, default value, etc.
Users Table in MySQL database:1. Type the following command: “describe user;”2. Among the other fields, “host”,”user”and “password” are the fields of interest.3. “select host,user,password from user” command, to display a table of hosts, users andpasswords.If you want to be explicit, you can say mysql.user, makes it a little easier toread without having to rely on the “use” command that we created earlier.4. The passwords displayed in the table are encrypted versions of the password: thepassword is in plain text, but in the database,it is stored in this fashion. In some of the
higher security systems, the password is often actually shared in pieces between twoor three different users so that they all have to be there before the password becomesaccessible.5. In all the entries of the user, it is attached to a host, so as far as the MySQL databaseis concerned, only from the localhost, one can interact with the database.
NOTE: In reality, databases usually sit by themselves on a separate machine. The machinescan be clients that are explicitly declared. So when web servers that are part of the applicationcontact these clients, they also use the databases.